<?php

set_include_path($_SERVER ['DOCUMENT_ROOT']);
ini_set('memory_limit', '750M');
ini_set('set_time_limit', 1800);
include_once 'admin/includes/functions/admin-functions.php';

//TODO:: This needs to be completed. I do not understand how the system has been put together enough to do this without spending lots of time on it.
/**
 * Valid Filters:.
 *
 *    [weekdays] = this is the day of the week (1=Monday, 2=Tuesday, 3=Wednesday, 4=Thursday, 5=Friday, 6=Saturday, 7=Sunday
 *    [agegroups] = age group id
 *    [time] = time of day in 24hr
 *
 * @param string $id
 * @param array  $terms     An array of the term ids to list
 * @param array  $locations An array of the location ids to list
 * @param int    $week      The week number representing a particular week of the term
 * @param array  $filters
 */
function studentlist($id = null, $terms = array(), $locations = array(), $week = null, $filters = array())
{
    global $DBobject;
    $data = array();

    $fparams = array();
    if (!empty($_SESSION['user']['admin']['franchisee'])) {
        $n = 1;
        $tmpsql = array();
        foreach ($_SESSION['user']['admin']['franchisee'] as $m) {
            $tmpsql[] = "location_franchisee_id = :franchisee{$n}";
            $fparams[":franchisee{$n}"] = $m;
            ++$n;
        }
        $sql_franchisee = ' AND ('.implode(' OR ', $tmpsql).') ';
    }
  //This sql gets the term start day as the Sunday begining the week of the selected date SELECT DAYNAME(term_start_date), DAYOFWEEK(term_start_date),SUBDATE(term_start_date, (DAYOFWEEK(term_start_date)-1)),term_start_date FROM tbl_term
    if (empty($terms)) {
        //$tsql = "SELECT term_object_id FROM tbl_term WHERE term_deleted IS NULL AND term_published = 1 AND term_start_date <= NOW() AND term_end_date >= NOW()";
        $tsql = "SELECT term_object_id FROM tbl_term WHERE term_deleted IS NULL AND term_published = 1 AND term_start_date <= NOW() AND term_end_date >= NOW() AND term_id IN (SELECT DISTINCT(classterm_term_id) FROM tbl_classterm LEFT JOIN tbl_class ON classterm_class_id = class_object_id WHERE class_deleted IS NULL AND classterm_deleted IS NULL AND classterm_published = 1 AND class_location_id IN (SELECT location_object_id FROM tbl_location WHERE location_deleted IS NULL {$sql_franchisee})) ORDER BY term_start_date DESC";
        $res = $DBobject->wrappedSql($tsql, $fparams);
        foreach ($res as $r) {
            $terms[] = $r['term_object_id'];
        }
        if (empty($terms)) {
            return array();
        }
    }
    $cltparams = array();
    $clweGTids = array();
    $clweLTids = array();
    $clweparams = array();
    $trials_clweGTids = array();
    $trials_clweparams = array();

  //Get week numbers for all terms or assign static week number if selected
    $n = 1;
    $termlist = array();
    foreach ($terms as $id) {
        $wsql = 'SELECT term_name,term_region, term_id, term_object_id, term_start_date, SUBDATE(term_start_date, (DAYOFWEEK(term_start_date)-1)) AS firstsunday, '.(empty($week) ? 'IF(term_start_date < NOW(),CEIL(DATEDIFF(IF(term_end_date>NOW(),NOW(),term_start_date),SUBDATE(term_start_date, (DAYOFWEEK(term_start_date)-1)))/7),1)' : "{$week}").' AS weekno FROM tbl_term WHERE term_deleted IS NULL AND term_published = 1 AND term_object_id = :term_id';
        $tres = $DBobject->wrappedSql($wsql, array('term_id' => $id));
        $termlist[] = $tres[0];

        $clweGTids[] = "(enrolment_term_object_id = :term_id{$n} AND enrolment_week >= :week{$n})";
        $clweLTids[] = "(enrolment_term_object_id = :term_id{$n} AND enrolment_week < :week{$n})";
        $clweparams["term_id{$n}"] = $tres[0]['term_object_id'];
        $clweparams["week{$n}"] = $tres[0]['weekno'];
    //Trials
        if ($tres[0]['weekno'] > 1) {
            $trials_clweGTids[] = "(enrolment_term_object_id = :trials_term_id{$n} AND enrolment_week = :trials_week{$n})";
            $trials_clweparams["trials_term_id{$n}"] = $tres[0]['term_object_id'];
            $trials_clweparams["trials_week{$n}"] = ($tres[0]['weekno'] - 1);
        }
        ++$n;
    }
    $clweGLTwhere = ' AND ('.implode(' OR ', $clweGTids).')';
    $clweLTwhere = ' AND ('.implode(' OR ', $clweLTids).')';

    if (!empty($trials_clweGTids)) {
        $trials_clweLTwhere = ' AND ('.implode(' OR ', $trials_clweGTids).')';
    }

  //Get Class record to show all classes for the selected term
    $lparams = array();
    $lids = array();
    $lwhere = '';
    if (!empty($locations)) {
        $nl = 0;
        foreach ($locations as $id) {
            $lids[] = "class_location_id = :local_id{$nl}";
            $lparams["local_id{$nl}"] = $id;
            ++$nl;
        }
        $lwhere = ' AND ('.implode(' OR ', $lids).')';
    }

  //Filter by region
    if (!empty($filters) && !empty($filters['regions'])) {
        $rdsql = array();
        $nr = 0;
        foreach ($filters['regions'] as $rg) {
            $fparams["region{$nr}"] = $rg;
            $rdsql[] = "location_region_id=:region{$nr}";
            ++$nr;
        }
        $sql_region = ' AND ('.implode(' OR ', $rdsql).') ';
    }

    foreach ($termlist as &$t) {
        $locsql = "SELECT tbl_location.* FROM tbl_location LEFT JOIN tbl_class ON class_location_id = location_object_id LEFT JOIN tbl_classterm ON classterm_class_id = class_object_id WHERE location_deleted IS NULL AND location_published = 1 AND class_deleted IS NULL AND class_published = 1 AND classterm_deleted IS NULL AND classterm_published = 1 {$sql_franchisee}{$sql_region} AND classterm_term_id = :term_id{$lwhere} GROUP BY location_object_id ORDER BY location_state, location_suburb";
        $locs = $DBobject->wrappedSql($locsql, array_merge($lparams, array(':term_id' => $t['term_id']), $fparams));

        foreach ($locs as &$l) {
            $cltermwhere = ' AND classterm_term_id = :term_id AND class_location_id = :loc_id';
            $cltparams['term_id'] = $t['term_id'];
            $cltparams['loc_id'] = $l['location_object_id'];

            if (!empty($filters) && !empty($filters['weekdays'])) {
                $wdsql = array();
                $dwn = 0;
                foreach ($filters['weekdays'] as $wd) {
                    $cltparams["weekday{$dwn}"] = $wd;
                    $wdsql[] = "class_weekday like :weekday{$dwn}";
                    ++$dwn;
                }
                $cltermwhere .= ' AND ('.implode(' OR ', $wdsql).') ';
            }
            if (!empty($filters) && !empty($filters['agegroups'])) {
                $agsql = array();
                $agn = 0;
                foreach ($filters['agegroups'] as $wd) {
                    $cltparams["agegroup{$agn}"] = $wd;
                    $agsql[] = "class_agegroup_id=:agegroup{$agn}";
                    ++$agn;
                }
                $cltermwhere .= ' AND ('.implode(' OR ', $agsql).') ';
            }

            $lsql = "SELECT class_object_id,class_name,class_weekday,class_url,location_name, agegroup_name,class_size FROM tbl_class LEFT JOIN tbl_location ON class_location_id = location_object_id LEFT JOIN tbl_agegroup ON class_agegroup_id = agegroup_id LEFT JOIN tbl_classterm ON classterm_class_id = class_object_id WHERE class_deleted IS NULL AND class_published = 1{$lwhere} AND agegroup_deleted IS NULL AND location_deleted IS NULL AND classterm_published = 1 AND classterm_deleted IS NULL {$cltermwhere} ORDER BY location_suburb, FIELD(class_weekday, 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY', 'SUNDAY'), class_start_time, agegroup_name";
            $classes = $DBobject->wrappedSql($lsql, array_merge($lparams, $cltparams));

            foreach ($classes as &$c) {
                //If class was within last 2 days of this week
                $pck = date('N', strtotime($c['class_weekday']));
                $rck = date('N', time());
                $past = false;
                if ($rck < $pck) {
                    $rck = $rck + 7;
                } else {
                    $past = true;
                }
                $dck = $rck - $pck;

                $GTweekStudents = "SELECT enrolment_student_id, enrolment_type, enrolment_term_object_id, enrolment_class_object_id, enrolment_week, student_name,student_surname,student_dob,user_gname,user_surname,user_email,enrolment_type, status_name, user_id FROM
                  tbl_enrolment LEFT JOIN tbl_student ON enrolment_student_id = student_id LEFT JOIN tbl_user ON student_user_id = user_id LEFT JOIN tbl_order ON enrolment_payment_id = order_payment_id LEFT JOIN tbl_status ON order_status_id = status_id
                  WHERE enrolment_deleted IS NULL AND order_deleted IS NULL AND enrolment_published = 1 {$clweGLTwhere} AND enrolment_class_object_id = :class_id AND enrolment_term_object_id = :term_id GROUP BY enrolment_student_id,enrolment_type,enrolment_term_object_id,enrolment_class_object_id,enrolment_week ORDER BY enrolment_term_object_id,enrolment_class_object_id, user_id, enrolment_type = 'S' DESC ";
                $students = $DBobject->wrappedSql($GTweekStudents, array_merge(array(':class_id' => $c['class_object_id'], ':term_id' => $t['term_object_id']), $clweparams));
                $check = array();
                foreach ($students as $stu) {
                    $chkval = $stu['enrolment_student_id'].$stu['enrolment_term_object_id'].$stu['enrolment_class_object_id'];
                    if (in_array($chkval, $check) && $stu['enrolment_type'] != 'MK') {
                        continue;
                    }
                    //Ignore trials older than 2 days
                    $_date = getDateByWeekdayStartDate($c['class_weekday'], $t['term_start_date'], $stu['enrolment_week'] - 1, 'Y-m-d');
                    $_ddif = dateDifference($_date, date('Y-m-d'));
                    if ($_ddif > 2 && $stu['enrolment_type'] == 'T') {
                        continue;
                    }
                    if ($_ddif > 0 && $stu['enrolment_type'] == 'MK') {
                        continue;
                    }
                    if ($_ddif > 0 && $stu['enrolment_type'] == 'T') {
                        $stu['trial_past'] = 1;
                    }
                    //if($stu['enrolment_type'] == 'T' && $_SERVER['REMOTE_ADDR'] == '150.101.230.130' ){var_dump($stu['enrolment_week']); var_dump(date('Y-m-d'));die(var_dump($_ddif)); }

                    $adrsql = 'SELECT address_telephone, address_line1, address_suburb, address_postcode FROM tbl_address WHERE address_user_id = :id ORDER BY address_modified DESC LIMIT 1';
                    $address = $DBobject->wrappedSql($adrsql, array(':id' => $stu['user_id']));
                    if (!empty($address)) {
                        $stu = array_merge($stu, $address[0]);
                    }
                    $check[] = $chkval;
                    $c['students'][] = $stu;
                }

                if ($pck >= 6 && !empty($trials_clweLTwhere)) {
                    $trials_GTweekStudents = "SELECT enrolment_student_id, enrolment_type, enrolment_term_object_id, enrolment_class_object_id, enrolment_week, student_name,student_surname,student_dob,user_gname,user_surname,user_email,enrolment_type, status_name, user_id FROM tbl_enrolment LEFT JOIN tbl_student ON enrolment_student_id = student_id LEFT JOIN tbl_user ON student_user_id = user_id LEFT JOIN tbl_order ON enrolment_payment_id = order_payment_id LEFT JOIN tbl_status ON order_status_id = status_id WHERE enrolment_deleted IS NULL AND order_deleted IS NULL AND enrolment_published = 1 {$trials_clweLTwhere} AND enrolment_type = 'T' AND enrolment_class_object_id = :class_id AND enrolment_term_object_id = :term_id GROUP BY enrolment_student_id,enrolment_type,enrolment_term_object_id,enrolment_class_object_id,enrolment_week ORDER BY enrolment_term_object_id,enrolment_class_object_id, user_id, enrolment_type = 'S' DESC ";
                    $students = $DBobject->wrappedSql($trials_GTweekStudents, array_merge(array(':class_id' => $c['class_object_id'], ':term_id' => $t['term_object_id']), $trials_clweparams));
                    $check = array();
                    foreach ($students as $stu) {
                        $chkval = $stu['enrolment_student_id'].$stu['enrolment_term_object_id'].$stu['enrolment_class_object_id'];
                        if (in_array($chkval, $check) && $stu['enrolment_type'] != 'MK') {
                            continue;
                        }

                        //Ignore trials older than 2 days
                        $_date = getDateByWeekdayStartDate($c['class_weekday'], $t['term_start_date'], $stu['enrolment_week'] - 1, 'Y-m-d');
                        $_ddif = dateDifference($_date, date('Y-m-d'));
                        if ($_ddif > 2 && $stu['enrolment_type'] == 'T') {
                            continue;
                        }
                        if ($_ddif <= 0  && $stu['enrolment_type'] == 'T') {
                            continue;
                        }

                        $adrsql = 'SELECT address_telephone, address_line1, address_suburb, address_postcode FROM tbl_address WHERE address_user_id = :id ORDER BY address_modified DESC LIMIT 1';
                        $address = $DBobject->wrappedSql($adrsql, array(':id' => $stu['user_id']));
                        if (!empty($address)) {
                            $stu = array_merge($stu, $address[0]);
                        }
                        $stu['trial_past'] = 1;
                        $check[] = $chkval;
                        $c['students'][] = $stu;
                    }
                }

                $LTweekStudents = "SELECT enrolment_student_id, enrolment_type, enrolment_term_object_id, enrolment_class_object_id, enrolment_week, student_name,student_surname,student_dob,user_gname,user_surname,user_email,enrolment_type, status_name, user_id FROM tbl_enrolment LEFT JOIN tbl_student ON enrolment_student_id = student_id LEFT JOIN tbl_user ON student_user_id = user_id LEFT JOIN tbl_order ON enrolment_payment_id = order_payment_id LEFT JOIN tbl_status ON order_status_id = status_id WHERE enrolment_deleted IS NULL AND order_deleted IS NULL AND enrolment_published = 1 {$clweLTwhere} AND enrolment_class_object_id = :class_id AND enrolment_term_object_id = :term_id GROUP BY enrolment_student_id,enrolment_type,enrolment_term_object_id,enrolment_class_object_id,enrolment_week ORDER BY enrolment_term_object_id,enrolment_class_object_id, user_id, enrolment_type = 'S' DESC ";
                $students = $DBobject->wrappedSql($LTweekStudents, array_merge(array(':class_id' => $c['class_object_id'], ':term_id' => $t['term_object_id']), $clweparams));
                foreach ($students as $stu) {
                    $chkval = $stu['enrolment_student_id'].$stu['enrolment_term_object_id'].$stu['enrolment_class_object_id'];
                    if (in_array($chkval, $check) && $stu['enrolment_type'] != 'MK') {
                        continue;
                    }
                    $adrsql = 'SELECT address_telephone FROM tbl_address WHERE address_user_id = :id ORDER BY address_modified DESC LIMIT 1';
                    $address = $DBobject->wrappedSql($adrsql, array(':id' => $stu['user_id']));
                    if (!empty($address)) {
                        $stu = array_merge($stu, $address[0]);
                    }
                    $check[] = $chkval;
                    $c['oldstudents'][] = $stu;
                }

                //WAIT LIST
                $WaitListSQL = 'SELECT waitlist_id, waitlist_student_id, waitlist_term_object_id, waitlist_class_object_id, student_name,student_surname,student_dob,user_gname,user_surname,user_email,user_id FROM tbl_waitlist LEFT JOIN tbl_student ON waitlist_student_id = student_id LEFT JOIN tbl_user ON student_user_id = user_id WHERE waitlist_deleted IS NULL AND waitlist_term_object_id = :term_id AND waitlist_class_object_id = :class_id GROUP BY waitlist_student_id, waitlist_term_object_id, waitlist_class_object_id ORDER BY waitlist_term_object_id, waitlist_class_object_id, user_id, waitlist_id DESC ';
                $students = $DBobject->wrappedSql($WaitListSQL, array(':class_id' => $c['class_object_id'], ':term_id' => $t['term_object_id']));
                foreach ($students as $stu) {
                    $adrsql = 'SELECT address_telephone, address_line1, address_suburb, address_postcode FROM tbl_address WHERE address_user_id = :id ORDER BY address_modified DESC LIMIT 1';
                    $address = $DBobject->wrappedSql($adrsql, array(':id' => $stu['user_id']));
                    if (!empty($address)) {
                        $stu = array_merge($stu, $address[0]);
                    }
                    $stu['enrolment_type'] = 'W';
                    $sql = 'SELECT type_name FROM tbl_type WHERE type_deleted IS NULL AND type_code = :type_code';
                    $typeArr = $DBobject->wrappedSql($sql, array(':type_code' => $stu['enrolment_type']));
                    $stu['status_name'] = $typeArr[0]['type_name'];
                    $c['students'][] = $stu;
                }
            }

            if (!empty($classes)) {
                $l['classes'] = $classes;
            }
        }
        if (!empty($locs)) {
            $t['locations'] = $locs;
        }
    }

    return $termlist;
}

/**
 * Valid Filters:.
 *
 *    [weekdays] = this is the day of the week (1=Monday, 2=Tuesday, 3=Wednesday, 4=Thursday, 5=Friday, 6=Saturday, 7=Sunday
 *    [agegroups] = age group id
 *    [time] = time of day in 24hr
 *
 * @param string $id
 * @param array  $terms     An array of the term ids to list
 * @param array  $locations An array of the location ids to list
 * @param int    $week      The week number representing a particular week of the term
 * @param array  $filters
 */
function revenuelist($id = null, $terms = array(), $locations = array(), $filters = array())
{
    global $DBobject;
    $data = array();

    $fparams = array();
    if (!empty($_SESSION['user']['admin']['franchisee'])) {
        $n = 1;
        $tmpsql = array();
        foreach ($_SESSION['user']['admin']['franchisee'] as $m) {
            $tmpsql[] = "location_franchisee_id = :franchisee{$n}";
            $fparams[":franchisee{$n}"] = $m;
            ++$n;
        }
        $sql_franchisee = ' AND ('.implode(' OR ', $tmpsql).') ';
    }

  //This sql gets the term start day as the Sunday begining the week of the selected date SELECT DAYNAME(term_start_date), DAYOFWEEK(term_start_date),SUBDATE(term_start_date, (DAYOFWEEK(term_start_date)-1)),term_start_date FROM tbl_term
    if (empty($terms)) {
        //     $tsql = "SELECT term_object_id FROM tbl_term WHERE term_deleted IS NULL AND term_published = 1 AND term_start_date <= NOW() AND term_end_date >= NOW()";
    //     $res = $DBobject->wrappedSql($tsql);
        $tsql = "SELECT term_object_id FROM tbl_term WHERE term_deleted IS NULL AND term_published = 1 AND term_start_date <= NOW() AND term_end_date >= NOW() AND term_id IN (SELECT DISTINCT(classterm_term_id) FROM tbl_classterm LEFT JOIN tbl_class ON classterm_class_id = class_object_id WHERE class_deleted IS NULL AND classterm_deleted IS NULL AND classterm_published = 1 AND class_location_id IN (SELECT location_object_id FROM tbl_location WHERE location_deleted IS NULL {$sql_franchisee})) ORDER BY term_start_date DESC";
        $res = $DBobject->wrappedSql($tsql, $fparams);
        foreach ($res as $r) {
            $terms[] = $r['term_object_id'];
        }
        if (empty($terms)) {
            return array();
        }
    }
    $cltparams = array();
  //Get week numbers for all terms or assign static week number if selected
    $n = 1;
    $termlist = array();
    foreach ($terms as $id) {
        $wsql = 'SELECT term_name,term_region, term_id, term_object_id, SUBDATE(term_start_date, (DAYOFWEEK(term_start_date)-1)) AS firstsunday, '.(empty($week) ? 'IF(term_start_date < NOW(),CEIL(DATEDIFF(IF(term_end_date>NOW(),NOW(),term_start_date),SUBDATE(term_start_date, (DAYOFWEEK(term_start_date)-1)))/7),1)' : "{$week}").' AS weekno FROM tbl_term WHERE term_deleted IS NULL AND term_published = 1 AND term_object_id = :term_id';
        $tres = $DBobject->wrappedSql($wsql, array('term_id' => $id));
        $termlist[] = $tres[0];
        ++$n;
    }

  //Get Class record to show all classes for the selected term
    $lparams = array();
    $lids = array();
    $lwhere = '';
    $ln = 0;
    if (!empty($locations)) {
        foreach ($locations as $id) {
            $lids[] = "class_location_id = :local_id{$ln}";
            $lparams["local_id{$ln}"] = $id;
            ++$ln;
        }
        $lwhere = ' AND ('.implode(' OR ', $lids).')';
    }

    //Filter by region
    if (!empty($filters) && !empty($filters['regions'])) {
        $rdsql = array();
        $nr = 0;
        foreach ($filters['regions'] as $rg) {
            $fparams["region{$nr}"] = $rg;
            $rdsql[] = "location_region_id=:region{$nr}";
            ++$nr;
        }
        $sql_region = ' AND ('.implode(' OR ', $rdsql).') ';
    }

    foreach ($termlist as &$t) {
        $locsql = "SELECT * FROM tbl_location WHERE location_deleted IS NULL AND location_published = 1 {$sql_franchisee}{$sql_region} AND EXISTS (SELECT classterm_id FROM tbl_classterm LEFT JOIN tbl_class ON classterm_class_id = class_object_id WHERE class_location_id = location_object_id AND class_deleted IS NULL AND class_published = 1 AND classterm_deleted IS NULL AND classterm_published = 1 AND classterm_term_id = :term_id{$lwhere}) ORDER BY location_state, location_suburb";
        $locations = $DBobject->wrappedSql($locsql, array_merge($lparams, array(':term_id' => $t['term_id']), $fparams));
        foreach ($locations as &$l) {
            $cltermwhere = ' AND classterm_term_id = :term_id AND class_location_id = :loc_id';
            $cltparams['term_id'] = $t['term_id'];
            $cltparams['loc_id'] = $l['location_object_id'];

            if (!empty($filters) && !empty($filters['weekdays'])) {
                $wdsql = array();
                $dwn = 0;
                foreach ($filters['weekdays'] as $wd) {
                    $cltparams["weekday{$dwn}"] = $wd;
                    $wdsql[] = "class_weekday like :weekday{$dwn}";
                    ++$dwn;
                }
                $cltermwhere .= ' AND ('.implode(' OR ', $wdsql).') ';
            }
            if (!empty($filters) && !empty($filters['agegroups'])) {
                $agsql = array();
                $agn = 0;
                foreach ($filters['agegroups'] as $wd) {
                    $cltparams["agegroup{$agn}"] = $wd;
                    $agsql[] = "class_agegroup_id=:agegroup{$agn}";
                    ++$agn;
                }
                $cltermwhere .= ' AND ('.implode(' OR ', $agsql).') ';
            }
            $payparams = array();
            $paymwhere = '';
            if (!empty($filters) && !empty($filters['start_date'])) {
                $agsql = array();
                $payparams['start_date'] = date('Y-m-d', strtotime(str_replace('/', '-', $filters['start_date'])));
                $paymwhere .= ' AND DATE(payment_created) >= :start_date ';
            }
            if (!empty($filters) && !empty($filters['end_date'])) {
                $agsql = array();
                $payparams['end_date'] = date('Y-m-d', strtotime(str_replace('/', '-', $filters['end_date'])));
                $paymwhere .= ' AND DATE(payment_created) <= :end_date ';
            }

            $lsql = "SELECT class_object_id,class_name,class_weekday,class_url,location_name, agegroup_name,class_size FROM tbl_class LEFT JOIN tbl_location ON class_location_id = location_object_id LEFT JOIN tbl_agegroup ON class_agegroup_id = agegroup_id  WHERE class_deleted IS NULL AND class_published = 1{$lwhere} AND agegroup_deleted IS NULL AND location_deleted IS NULL AND EXISTS (SELECT classterm_id FROM tbl_classterm WHERE classterm_class_id = class_object_id AND classterm_deleted IS NULL {$cltermwhere}) ORDER BY location_suburb, FIELD(class_weekday, 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY', 'SUNDAY'), class_start_time, agegroup_name";
            $classes = $DBobject->wrappedSql($lsql, array_merge($lparams, $cltparams));

      //get all information for students enrolled in class days after today
            $sum = 0;
            foreach ($classes as &$c) {
                $sql_filters = " AND payment_id IN (SELECT order_payment_id FROM tbl_order LEFT JOIN tbl_status ON order_status_id = status_id WHERE order_deleted IS NULL AND status_category != 'cancelled' AND status_category != 'refunded' AND status_type = 'full' AND status_category = 'approved' )";
                $psql_filters = " AND payment_id IN (SELECT order_payment_id FROM tbl_order LEFT JOIN tbl_status ON order_status_id = status_id WHERE order_deleted IS NULL AND status_category != 'cancelled' AND status_category != 'refunded' AND status_type = 'partial' AND status_category = 'approved' )";
        //$rsql = "SELECT SUM(payment_total) AS revenue FROM tbl_payment WHERE payment_status = 'A' AND payment_deleted IS NULL {$paymwhere} AND EXISTS (SELECT * FROM tbl_enrolment WHERE (enrolment_type != 'MK' AND enrolment_type != 'T') AND enrolment_payment_id = payment_id AND enrolment_published = 1 AND enrolment_deleted IS NULL AND enrolment_class_object_id = :class_id AND enrolment_term_object_id = :term_id) {$sql_filters}";
                $subrsql = "SELECT payment_total AS total, payment_id FROM tbl_payment LEFT JOIN tbl_enrolment ON payment_id = enrolment_payment_id WHERE payment_status = 'A' AND payment_deleted IS NULL {$paymwhere} AND enrolment_type != 'MK' AND enrolment_type != 'T' AND enrolment_published = 1 AND enrolment_deleted IS NULL AND enrolment_class_object_id = :class_id AND enrolment_term_object_id = :term_id {$sql_filters} GROUP BY payment_id";
                $rsql = "SELECT SUM(total) AS revenue FROM ({$subrsql}) AS TMP";

        //if($_SERVER['REMOTE_ADDR']=='150.101.230.130') { var_dump($rsql);var_dump(array_merge(array(":class_id"=>$c["class_object_id"],":term_id"=>$t['term_object_id']),$payparams)); die();}

                $ressum = $DBobject->wrappedSql($rsql, array_merge(array(':class_id' => $c['class_object_id'], ':term_id' => $t['term_object_id']), $payparams));

        //$rsql2 = "SELECT SUM(payment_charged_amount) AS revenue FROM tbl_payment WHERE payment_status = 'A' AND payment_deleted IS NULL {$paymwhere} AND EXISTS (SELECT * FROM tbl_enrolment WHERE (enrolment_type != 'MK' AND enrolment_type != 'T') AND enrolment_payment_id = payment_id AND enrolment_published = 1 AND enrolment_deleted IS NULL AND enrolment_class_object_id = :class_id AND enrolment_term_object_id = :term_id) {$psql_filters}";
                $subrsql2 = "SELECT payment_charged_amount AS total, payment_id FROM tbl_payment LEFT JOIN tbl_enrolment ON payment_id = enrolment_payment_id WHERE payment_status = 'A' AND payment_deleted IS NULL {$paymwhere} AND enrolment_type != 'MK' AND enrolment_type != 'T' AND enrolment_published = 1 AND enrolment_deleted IS NULL AND enrolment_class_object_id = :class_id AND enrolment_term_object_id = :term_id {$psql_filters} GROUP BY payment_id";
                $rsql2 = "SELECT SUM(total) AS revenue FROM ({$subrsql2}) AS TMP";
                $ressum2 = $DBobject->wrappedSql($rsql2, array_merge(array(':class_id' => $c['class_object_id'], ':term_id' => $t['term_object_id']), $payparams));

                setlocale(LC_MONETARY, 'en_AU');
                $c['revenue'] = money_format('%i', $ressum[0]['revenue'] + $ressum2[0]['revenue']);
                $sum += $ressum[0]['revenue'];
            }

            $l['total'] = money_format('%i', $sum);
            if (!empty($classes)) {
                $l['classes'] = $classes;
            }
        }
        if (!empty($locations)) {
            $t['locations'] = $locations;
        }
    }

    return $termlist;
}

/**
 * Valid Filters:.
 *
 *    [weekdays] = this is the day of the week (1=Monday, 2=Tuesday, 3=Wednesday, 4=Thursday, 5=Friday, 6=Saturday, 7=Sunday
 *    [agegroups] = age group id
 *    [time] = time of day in 24hr
 *
 * @param string $id
 * @param array  $terms     An array of the term ids to list
 * @param array  $locations An array of the location ids to list
 * @param int    $week      The week number representing a particular week of the term
 * @param array  $filters
 */
function enrolmentlist($id = null, $terms = array(), $locations = array(), $filters = array(), $enrolmenttype = array())
{
    global $DBobject;

    $fparams = array();
    if (!empty($_SESSION['user']['admin']['franchisee'])) {
        $n = 1;
        $tmpsql = array();
        foreach ($_SESSION['user']['admin']['franchisee'] as $m) {
            $tmpsql[] = "location_franchisee_id = :franchisee{$n}";
            $fparams[":franchisee{$n}"] = $m;
            ++$n;
        }
        $sql_franchisee = ' AND ('.implode(' OR ', $tmpsql).') ';
    }

  //This sql gets the term start day as the Sunday begining the week of the selected date SELECT DAYNAME(term_start_date), DAYOFWEEK(term_start_date),SUBDATE(term_start_date, (DAYOFWEEK(term_start_date)-1)),term_start_date FROM tbl_term
    if (empty($terms)) {
        //     $tsql = "SELECT term_object_id FROM tbl_term WHERE term_deleted IS NULL AND term_published = 1 AND term_start_date <= NOW() AND term_end_date >= NOW()";
    //     $res = $DBobject->wrappedSql($tsql);
        $tsql = "SELECT term_object_id FROM tbl_term WHERE term_deleted IS NULL AND term_published = 1 AND term_start_date <= NOW() AND term_end_date >= NOW() AND term_id IN (SELECT DISTINCT(classterm_term_id) FROM tbl_classterm LEFT JOIN tbl_class ON classterm_class_id = class_object_id WHERE class_deleted IS NULL AND classterm_deleted IS NULL AND classterm_published = 1 AND class_location_id IN (SELECT location_object_id FROM tbl_location WHERE location_deleted IS NULL {$sql_franchisee})) ORDER BY term_start_date DESC";
        $res = $DBobject->wrappedSql($tsql, $fparams);
        foreach ($res as $r) {
            $terms[] = $r['term_object_id'];
        }
        if (empty($terms)) {
            return array();
        }
    }
    $cltparams = array();
  //Get week numbers for all terms or assign static week number if selected
    $n = 1;
    $termlist = array();
    foreach ($terms as $id) {
        $wsql = 'SELECT term_name,term_region, term_id, term_object_id, SUBDATE(term_start_date, (DAYOFWEEK(term_start_date)-1)) AS firstsunday, '.(empty($week) ? 'IF(term_start_date < NOW(),CEIL(DATEDIFF(IF(term_end_date>NOW(),NOW(),term_end_date),SUBDATE(term_start_date, (DAYOFWEEK(term_start_date)-1)))/7),1)' : "{$week}").' AS weekno FROM tbl_term WHERE term_deleted IS NULL AND term_published = 1 AND term_object_id = :term_id';
        $tres = $DBobject->wrappedSql($wsql, array('term_id' => $id));
        $termlist[] = $tres[0];
        ++$n;
    }

  //Get Class record to show all classes for the selected term
    $lparams = array();
    $lids = array();
    $lwhere = '';
    $ln = 0;
    if (!empty($locations)) {
        foreach ($locations as $id) {
            $lids[] = "class_location_id = :local_id{$ln}";
            $lparams["local_id{$ln}"] = $id;
            ++$ln;
        }
        $lwhere = ' AND ('.implode(' OR ', $lids).')';
    }

  //Filter by region
    if (!empty($filters) && !empty($filters['regions'])) {
        $rdsql = array();
        $nr = 0;
        foreach ($filters['regions'] as $rg) {
            $fparams["region{$nr}"] = $rg;
            $rdsql[] = "location_region_id=:region{$nr}";
            ++$nr;
        }
        $sql_region = ' AND ('.implode(' OR ', $rdsql).') ';
    }

    foreach ($termlist as &$t) {
        $t['total'] = 0;
        $t['totalTrial'] = 0;
        $t['totalPending'] = 0;
        $t['totalPaid'] = 0;

        $locsql = "SELECT * FROM tbl_location WHERE location_deleted IS NULL AND location_published = 1 {$sql_franchisee}{$sql_region} AND EXISTS (SELECT classterm_id FROM tbl_classterm LEFT JOIN tbl_class ON classterm_class_id = class_object_id WHERE class_location_id = location_object_id AND class_deleted IS NULL AND class_published = 1 AND classterm_deleted IS NULL AND classterm_published = 1 AND classterm_term_id = :term_id{$lwhere}) ORDER BY location_state, location_suburb";
        $locations = $DBobject->wrappedSql($locsql, array_merge($lparams, array(':term_id' => $t['term_id']), $fparams));
        foreach ($locations as &$l) {
            $cltermwhere = ' AND classterm_term_id = :term_id AND class_location_id = :loc_id';
            $cltparams['term_id'] = $t['term_id'];
            $cltparams['loc_id'] = $l['location_object_id'];

            if (!empty($filters) && !empty($filters['weekdays'])) {
                $wdsql = array();
                $dwn = 0;
                foreach ($filters['weekdays'] as $wd) {
                    $cltparams["weekday{$dwn}"] = $wd;
                    $wdsql[] = "class_weekday like :weekday{$dwn}";
                    ++$dwn;
                }
                $cltermwhere .= ' AND ('.implode(' OR ', $wdsql).') ';
            }
            if (!empty($filters) && !empty($filters['agegroups'])) {
                $agsql = array();
                $agn = 0;
                foreach ($filters['agegroups'] as $wd) {
                    $cltparams["agegroup{$agn}"] = $wd;
                    $agsql[] = "class_agegroup_id=:agegroup{$agn}";
                    ++$agn;
                }
                $cltermwhere .= ' AND ('.implode(' OR ', $agsql).') ';
            }
            $payparams = array();
            $paymwhere = '';
            if (!empty($filters) && !empty($filters['start_date'])) {
                $agsql = array();
                $payparams['start_date'] = date('Y-m-d', strtotime(str_replace('/', '-', $filters['start_date'])));
                $paymwhere .= ' AND DATE(payment_created) >= :start_date ';
            }
            if (!empty($filters) && !empty($filters['end_date'])) {
                $agsql = array();
                $payparams['end_date'] = date('Y-m-d', strtotime(str_replace('/', '-', $filters['end_date'])));
                $paymwhere .= ' AND DATE(payment_created) <= :end_date ';
            }

            $stn = 0;
            $stparams = array();
            $stsql = array();
            foreach ($enrolmenttype as $s) {
                $stparams["enrltype{$stn}"] = $s;
                $stsql[] = "enrolment_type=:enrltype{$stn}";
                ++$stn;
            }

            if (!empty($stsql)) {
                $sttermwhere = ' AND ('.implode(' OR ', $stsql).') ';
            }

            $lsql = "SELECT class_object_id,class_name,class_weekday,class_url,location_name, agegroup_name,agegroup_shortname,class_size FROM tbl_class LEFT JOIN tbl_location ON class_location_id = location_object_id LEFT JOIN tbl_agegroup ON class_agegroup_id = agegroup_id LEFT JOIN tbl_classterm ON classterm_class_id = class_object_id  WHERE class_deleted IS NULL AND class_published = 1 AND classterm_published = 1 AND classterm_deleted IS NULL {$lwhere} AND agegroup_deleted IS NULL AND location_deleted IS NULL {$cltermwhere} ORDER BY location_suburb, FIELD(class_weekday, 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY', 'SUNDAY'), class_start_time, agegroup_name";
            $classes = $DBobject->wrappedSql($lsql, array_merge($lparams, $cltparams));

      //get all information for students enrolled in class days after today
            $sum = 0;
            $sumTrial = 0;
            $sumPending = 0;
            $sumPaid = 0;
            foreach ($classes as &$c) {
                //Total
                $rsql = "SELECT COUNT(DISTINCT(CONCAT(enrolment_student_id))) AS cnt FROM tbl_enrolment LEFT JOIN tbl_payment ON enrolment_payment_id = payment_id WHERE enrolment_published = 1 AND enrolment_deleted IS NULL {$sttermwhere} AND enrolment_published = 1 AND enrolment_type != 'MK' AND enrolment_class_object_id = :class_id AND enrolment_term_object_id = :term_id AND enrolment_week >= :weekno AND payment_deleted IS NULL {$paymwhere}";
                $ressum = $DBobject->wrappedSql($rsql, array_merge(array(':class_id' => $c['class_object_id'], ':term_id' => $t['term_object_id'], ':weekno' => $t['weekno']), $stparams, $payparams));
                $c['enrolments'] = $ressum[0]['cnt'];
                $c['capacity'] = number_format(($ressum[0]['cnt'] / $c['class_size']) * 100, 2);
                $sum += $ressum[0]['cnt'];

        //Trial
                $rsql = "SELECT COUNT(DISTINCT(CONCAT(enrolment_student_id))) AS cnt FROM tbl_enrolment LEFT JOIN tbl_payment ON enrolment_payment_id = payment_id WHERE enrolment_published = 1 AND enrolment_deleted IS NULL {$sttermwhere} AND enrolment_published = 1 AND enrolment_type = 'T' AND enrolment_class_object_id = :class_id AND enrolment_term_object_id = :term_id AND enrolment_week >= :weekno AND payment_deleted IS NULL {$paymwhere}";
                $ressum = $DBobject->wrappedSql($rsql, array_merge(array(':class_id' => $c['class_object_id'], ':term_id' => $t['term_object_id'], ':weekno' => $t['weekno']), $stparams, $payparams));
                $c['trial'] = $ressum[0]['cnt'];
                $sumTrial += $ressum[0]['cnt'];

        //Pending and not Trial
                $rsql = "SELECT COUNT(DISTINCT(CONCAT(enrolment_student_id))) AS cnt FROM tbl_enrolment LEFT JOIN tbl_payment ON enrolment_payment_id = payment_id LEFT JOIN tbl_order ON order_payment_id = payment_id WHERE enrolment_published = 1 AND enrolment_deleted IS NULL {$sttermwhere} AND enrolment_published = 1 AND enrolment_type != 'MK' AND enrolment_type != 'T' AND enrolment_class_object_id = :class_id AND enrolment_term_object_id = :term_id AND enrolment_week >= :weekno AND payment_deleted IS NULL AND order_deleted IS NULL AND (order_status_id = 7 OR order_status_id = 8) {$paymwhere}";
                $ressum = $DBobject->wrappedSql($rsql, array_merge(array(':class_id' => $c['class_object_id'], ':term_id' => $t['term_object_id'], ':weekno' => $t['weekno']), $stparams, $payparams));
                $c['pending'] = $ressum[0]['cnt'];
                $sumPending += $ressum[0]['cnt'];

        //Paid and not Trial
                $rsql = "SELECT COUNT(DISTINCT(CONCAT(enrolment_student_id))) AS cnt FROM tbl_enrolment LEFT JOIN tbl_payment ON enrolment_payment_id = payment_id LEFT JOIN tbl_order ON order_payment_id = payment_id WHERE enrolment_published = 1 AND enrolment_deleted IS NULL {$sttermwhere} AND enrolment_published = 1 AND enrolment_type != 'MK' AND enrolment_type != 'T' AND enrolment_class_object_id = :class_id AND enrolment_term_object_id = :term_id AND enrolment_week >= :weekno AND payment_deleted IS NULL AND order_deleted IS NULL AND order_status_id > 0 AND order_status_id < 6 {$paymwhere}";
                $ressum = $DBobject->wrappedSql($rsql, array_merge(array(':class_id' => $c['class_object_id'], ':term_id' => $t['term_object_id'], ':weekno' => $t['weekno']), $stparams, $payparams));
                $c['paid'] = $ressum[0]['cnt'];
                $c['paidCapacity'] = number_format(($ressum[0]['cnt'] / $c['class_size']) * 100, 2);
                $sumPaid += $ressum[0]['cnt'];
            }
            $l['total'] = $sum;
            $l['totalTrial'] = $sumTrial;
            $l['totalPending'] = $sumPending;
            $l['totalPaid'] = $sumPaid;

            $t['total'] += $sum;
            $t['totalTrial'] += $sumTrial;
            $t['totalPending'] += $sumPending;
            $t['totalPaid'] += $sumPaid;
            if (!empty($classes)) {
                $l['classes'] = $classes;
            }
        }
        if (!empty($locations)) {
            $t['locations'] = $locations;
        }
    }

    return $termlist;
}

/**
 * Valid Filters:.
 *
 *    [weekdays] = this is the day of the week (1=Monday, 2=Tuesday, 3=Wednesday, 4=Thursday, 5=Friday, 6=Saturday, 7=Sunday
 *    [agegroups] = age group id
 *    [time] = time of day in 24hr
 *
 * @param string $id
 * @param array  $terms     An array of the term ids to list
 * @param array  $locations An array of the location ids to list
 * @param int    $week      The week number representing a particular week of the term
 * @param array  $filters
 */
function studentstatuslist($id = null, $terms = array(), $locations = array(), $filters = array())
{
    global $DBobject;

    $fparams = array();
    if (!empty($_SESSION['user']['admin']['franchisee'])) {
        $n = 1;
        $tmpsql = array();
        foreach ($_SESSION['user']['admin']['franchisee'] as $m) {
            $tmpsql[] = "location_franchisee_id = :franchisee{$n}";
            $fparams[":franchisee{$n}"] = $m;
            ++$n;
        }
        $sql_franchisee = ' AND ('.implode(' OR ', $tmpsql).') ';
    }

  //This sql gets the term start day as the Sunday begining the week of the selected date SELECT DAYNAME(term_start_date), DAYOFWEEK(term_start_date),SUBDATE(term_start_date, (DAYOFWEEK(term_start_date)-1)),term_start_date FROM tbl_term
    if (empty($terms)) {
        //     $tsql = "SELECT term_object_id FROM tbl_term WHERE term_deleted IS NULL AND term_published = 1 AND term_start_date <= NOW() AND term_end_date >= NOW()";
    //     $res = $DBobject->wrappedSql($tsql);
        $tsql = "SELECT term_object_id FROM tbl_term WHERE term_deleted IS NULL AND term_published = 1 AND term_start_date <= NOW() AND term_end_date >= NOW() AND term_id IN (SELECT DISTINCT(classterm_term_id) FROM tbl_classterm LEFT JOIN tbl_class ON classterm_class_id = class_object_id WHERE class_deleted IS NULL AND classterm_deleted IS NULL AND classterm_published = 1 AND class_location_id IN (SELECT location_object_id FROM tbl_location WHERE location_deleted IS NULL {$sql_franchisee})) ORDER BY term_start_date DESC";
        $res = $DBobject->wrappedSql($tsql, $fparams);
        foreach ($res as $r) {
            $terms[] = $r['term_object_id'];
        }
        if (empty($terms)) {
            return array();
        }
    }
    $cltparams = array();
  //Get week numbers for all terms or assign static week number if selected
    $n = 1;
    $termlist = array();
    foreach ($terms as $id) {
        $wsql = 'SELECT term_name,term_region, term_id, term_object_id, SUBDATE(term_start_date, (DAYOFWEEK(term_start_date)-1)) AS firstsunday, '.(empty($week) ? 'IF(term_start_date < NOW(),CEIL(DATEDIFF(IF(term_end_date>NOW(),NOW(),term_start_date),SUBDATE(term_start_date, (DAYOFWEEK(term_start_date)-1)))/7),1)' : "{$week}").' AS weekno FROM tbl_term WHERE term_deleted IS NULL AND term_published = 1 AND term_object_id = :term_id';
        $tres = $DBobject->wrappedSql($wsql, array('term_id' => $id));
        $termlist[] = $tres[0];
        ++$n;
    }

  //Get Class record to show all classes for the selected term
    $lparams = array();
    $lids = array();
    $lwhere = '';
    $ln = 0;
    if (!empty($locations)) {
        foreach ($locations as $id) {
            $lids[] = "class_location_id = :local_id{$ln}";
            $lparams["local_id{$ln}"] = $id;
            ++$ln;
        }
        $lwhere = ' AND ('.implode(' OR ', $lids).')';
    }

  //Filter by region
    if (!empty($filters) && !empty($filters['regions'])) {
        $rdsql = array();
        $nr = 0;
        foreach ($filters['regions'] as $rg) {
            $fparams["region{$nr}"] = $rg;
            $rdsql[] = "location_region_id=:region{$nr}";
            ++$nr;
        }
        $sql_region = ' AND ('.implode(' OR ', $rdsql).') ';
    }

    foreach ($termlist as &$t) {
        $locsql = "SELECT * FROM tbl_location WHERE location_deleted IS NULL AND location_published = 1 {$sql_franchisee}{$sql_region} AND EXISTS (SELECT classterm_id FROM tbl_classterm LEFT JOIN tbl_class ON classterm_class_id = class_object_id WHERE class_location_id = location_object_id AND class_deleted IS NULL AND class_published = 1 AND classterm_deleted IS NULL AND classterm_published = 1 AND classterm_term_id = :term_id{$lwhere}) ORDER BY location_state, location_suburb";
        $locations = $DBobject->wrappedSql($locsql, array_merge($lparams, array(':term_id' => $t['term_id']), $fparams));
        foreach ($locations as &$l) {
            $cltermwhere = ' AND classterm_term_id = :term_id AND class_location_id = :loc_id';
            $cltparams['term_id'] = $t['term_id'];
            $cltparams['loc_id'] = $l['location_object_id'];

            if (!empty($filters) && !empty($filters['weekdays'])) {
                $wdsql = array();
                $dwn = 0;
                foreach ($filters['weekdays'] as $wd) {
                    $cltparams["weekday{$dwn}"] = $wd;
                    $wdsql[] = "class_weekday like :weekday{$dwn}";
                    ++$dwn;
                }
                $cltermwhere .= ' AND ('.implode(' OR ', $wdsql).') ';
            }
            if (!empty($filters) && !empty($filters['agegroups'])) {
                $agsql = array();
                $agn = 0;
                foreach ($filters['agegroups'] as $wd) {
                    $cltparams["agegroup{$agn}"] = $wd;
                    $agsql[] = "class_agegroup_id=:agegroup{$agn}";
                    ++$agn;
                }
                $cltermwhere .= ' AND ('.implode(' OR ', $agsql).') ';
            }

            $lsql = "SELECT class_object_id,class_name,class_weekday,class_url,location_name, agegroup_name,class_size FROM tbl_class LEFT JOIN tbl_location ON class_location_id = location_object_id LEFT JOIN tbl_agegroup ON class_agegroup_id = agegroup_id  WHERE class_deleted IS NULL AND class_published = 1{$lwhere} AND agegroup_deleted IS NULL AND location_deleted IS NULL AND EXISTS (SELECT classterm_id FROM tbl_classterm WHERE classterm_class_id = class_object_id AND classterm_deleted IS NULL {$cltermwhere}) ORDER BY location_suburb, FIELD(class_weekday, 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY', 'SUNDAY'), class_start_time, agegroup_name";
            $classes = $DBobject->wrappedSql($lsql, array_merge($lparams, $cltparams));

      //get all information for students enrolled in class days after today
            foreach ($classes as &$c) {
                $sql_filters = " AND payment_id IN (SELECT order_payment_id FROM tbl_order LEFT JOIN tbl_status ON order_status_id = status_id WHERE order_deleted IS NULL AND status_type != 'partial' )";
                $psql_filters = " AND payment_id IN (SELECT order_payment_id FROM tbl_order LEFT JOIN tbl_status ON order_status_id = status_id WHERE order_deleted IS NULL AND status_type = 'partial' )";
                    //$psql ="SELECT payment_created,status_name, payment_total AS payment_charged_amount, user_gname, user_surname, user_email,address_telephone FROM tbl_payment LEFT JOIN tbl_order ON payment_id = order_payment_id LEFT JOIN tbl_status ON order_status_id = status_id LEFT JOIN tbl_user ON payment_user_id = user_id LEFT JOIN tbl_address ON payment_billing_address_id = address_id WHERE payment_deleted IS NULL AND order_deleted IS NULL AND status_deleted IS NULL AND user_deleted IS NULL AND EXISTS (SELECT * FROM tbl_enrolment WHERE enrolment_deleted IS NULL AND enrolment_published = '1' AND enrolment_payment_id = payment_id AND enrolment_class_object_id = :class_id AND enrolment_term_object_id = :term_id) {$sql_filters} ORDER BY status_name,user_gname,user_surname";
                    $psql = "SELECT payment_created,status_name, payment_total AS payment_charged_amount, user_gname, user_surname, user_email,address_telephone FROM tbl_payment LEFT JOIN tbl_enrolment ON payment_id = enrolment_payment_id LEFT JOIN tbl_order ON payment_id = order_payment_id LEFT JOIN tbl_status ON order_status_id = status_id LEFT JOIN tbl_user ON payment_user_id = user_id LEFT JOIN tbl_address ON payment_billing_address_id = address_id WHERE payment_deleted IS NULL AND order_deleted IS NULL AND status_deleted IS NULL AND user_deleted IS NULL AND enrolment_deleted IS NULL AND enrolment_published = '1' AND enrolment_payment_id = payment_id AND enrolment_class_object_id = :class_id AND enrolment_term_object_id = :term_id {$sql_filters} GROUP BY payment_id ORDER BY status_name,user_gname,user_surname";
                $payments = $DBobject->wrappedSql($psql, array('class_id' => $c['class_object_id'], 'term_id' => $t['term_object_id']));
                if (empty($payments)) {
                    $payments = array();
                }
                    //$psql2 ="SELECT payment_created,status_name, payment_charged_amount, user_gname, user_surname, user_email,address_telephone FROM tbl_payment LEFT JOIN tbl_order ON payment_id = order_payment_id LEFT JOIN tbl_status ON order_status_id = status_id LEFT JOIN tbl_user ON payment_user_id = user_id LEFT JOIN tbl_address ON payment_billing_address_id = address_id WHERE payment_deleted IS NULL AND order_deleted IS NULL AND status_deleted IS NULL AND user_deleted IS NULL AND EXISTS (SELECT * FROM tbl_enrolment WHERE enrolment_deleted IS NULL AND enrolment_published = '1' AND enrolment_payment_id = payment_id AND enrolment_class_object_id = :class_id AND enrolment_term_object_id = :term_id) {$psql_filters} ORDER BY status_name,user_gname,user_surname";
                    $psql2 = "SELECT payment_created,status_name, payment_charged_amount, user_gname, user_surname, user_email,address_telephone FROM tbl_payment LEFT JOIN tbl_enrolment ON payment_id = enrolment_payment_id LEFT JOIN tbl_order ON payment_id = order_payment_id LEFT JOIN tbl_status ON order_status_id = status_id LEFT JOIN tbl_user ON payment_user_id = user_id LEFT JOIN tbl_address ON payment_billing_address_id = address_id WHERE payment_deleted IS NULL AND order_deleted IS NULL AND status_deleted IS NULL AND user_deleted IS NULL AND enrolment_deleted IS NULL AND enrolment_published = '1' AND enrolment_payment_id = payment_id AND enrolment_class_object_id = :class_id AND enrolment_term_object_id = :term_id {$psql_filters} GROUP BY payment_id ORDER BY status_name,user_gname,user_surname";
                $payments2 = $DBobject->wrappedSql($psql2, array('class_id' => $c['class_object_id'], 'term_id' => $t['term_object_id']));
                if (empty($payments2)) {
                    $payments2 = array();
                }
                if (!empty($payments)) {
                    $c['payments'] = array_merge($payments, $payments2);
                }
            }
            if (!empty($classes)) {
                $l['classes'] = $classes;
            }
        }
        if (!empty($locations)) {
            $t['locations'] = $locations;
        }
    }

    return $termlist;
}

/**
 * Valid Filters:.
 *
 *    [weekdays] = this is the day of the week (1=Monday, 2=Tuesday, 3=Wednesday, 4=Thursday, 5=Friday, 6=Saturday, 7=Sunday
 *    [agegroups] = age group id
 *    [time] = time of day in 24hr
 *
 * @param string $id
 * @param array  $terms     An array of the term ids to list
 * @param array  $locations An array of the location ids to list
 * @param int    $week      The week number representing a particular week of the term
 * @param array  $filters
 */
function dailypaymentlist($id = null, $terms = array(), $locations = array(), $filters = array())
{
    global $DBobject;

    $fparams = array();
    if (!empty($_SESSION['user']['admin']['franchisee'])) {
        $n = 1;
        $tmpsql = array();
        foreach ($_SESSION['user']['admin']['franchisee'] as $m) {
            $tmpsql[] = "location_franchisee_id = :franchisee{$n}";
            $fparams[":franchisee{$n}"] = $m;
            ++$n;
        }
        $sql_franchisee = ' AND ('.implode(' OR ', $tmpsql).') ';
    }

  //This sql gets the term start day as the Sunday begining the week of the selected date SELECT DAYNAME(term_start_date), DAYOFWEEK(term_start_date),SUBDATE(term_start_date, (DAYOFWEEK(term_start_date)-1)),term_start_date FROM tbl_term
    if (empty($terms)) {
        //     $tsql = "SELECT term_object_id FROM tbl_term WHERE term_deleted IS NULL AND term_published = 1 AND term_start_date <= NOW() AND term_end_date >= NOW()";
    //     $res = $DBobject->wrappedSql($tsql);
        $tsql = "SELECT term_object_id FROM tbl_term WHERE term_deleted IS NULL AND term_published = 1 AND term_start_date <= NOW() AND term_end_date >= NOW() AND term_id IN (SELECT DISTINCT(classterm_term_id) FROM tbl_classterm LEFT JOIN tbl_class ON classterm_class_id = class_object_id WHERE class_deleted IS NULL AND classterm_deleted IS NULL AND classterm_published = 1 AND class_location_id IN (SELECT location_object_id FROM tbl_location WHERE location_deleted IS NULL {$sql_franchisee})) ORDER BY term_start_date DESC";
        $res = $DBobject->wrappedSql($tsql, $fparams);
        foreach ($res as $r) {
            $terms[] = $r['term_object_id'];
        }
        if (empty($terms)) {
            return array();
        }
    }
    $cltparams = array();
  //Get week numbers for all terms or assign static week number if selected
    $n = 1;
    $termlist = array();
    foreach ($terms as $id) {
        $wsql = 'SELECT term_name,term_region, term_id, term_object_id, SUBDATE(term_start_date, (DAYOFWEEK(term_start_date)-1)) AS firstsunday, '.(empty($week) ? 'IF(term_start_date < NOW(),CEIL(DATEDIFF(IF(term_end_date>NOW(),NOW(),term_start_date),SUBDATE(term_start_date, (DAYOFWEEK(term_start_date)-1)))/7),1)' : "{$week}").' AS weekno FROM tbl_term WHERE term_deleted IS NULL AND term_published = 1 AND term_object_id = :term_id';
        $tres = $DBobject->wrappedSql($wsql, array('term_id' => $id));
        $termlist[] = $tres[0];
        ++$n;
    }

  //Get Class record to show all classes for the selected term
    $lparams = array();
    $lids = array();
    $lwhere = '';
    $ln = 0;
    if (!empty($locations)) {
        foreach ($locations as $id) {
            $lids[] = "class_location_id = :local_id{$ln}";
            $lparams["local_id{$ln}"] = $id;
            ++$ln;
        }
        $lwhere = ' AND ('.implode(' OR ', $lids).')';
    }

  //Filter by region
    if (!empty($filters) && !empty($filters['regions'])) {
        $rdsql = array();
        $nr = 0;
        foreach ($filters['regions'] as $rg) {
            $fparams["region{$nr}"] = $rg;
            $rdsql[] = "location_region_id=:region{$nr}";
            ++$nr;
        }
        $sql_region = ' AND ('.implode(' OR ', $rdsql).') ';
    }

    foreach ($termlist as &$t) {
        $locsql = "SELECT * FROM tbl_location WHERE location_deleted IS NULL AND location_published = 1 {$sql_franchisee}{$sql_region} AND EXISTS (SELECT classterm_id FROM tbl_classterm LEFT JOIN tbl_class ON classterm_class_id = class_object_id WHERE class_location_id = location_object_id AND class_deleted IS NULL AND class_published = 1 AND classterm_deleted IS NULL AND classterm_published = 1 AND classterm_term_id = :term_id{$lwhere}) ORDER BY location_state, location_suburb";
        $locations = $DBobject->wrappedSql($locsql, array_merge($lparams, array(':term_id' => $t['term_id']), $fparams));
        foreach ($locations as &$l) {
            $cltermwhere = ' AND classterm_term_id = :term_id AND class_location_id = :loc_id';
            $cltparams['term_id'] = $t['term_id'];
            $cltparams['loc_id'] = $l['location_object_id'];

            if (!empty($filters) && !empty($filters['weekdays'])) {
                $wdsql = array();
                $dwn = 0;
                foreach ($filters['weekdays'] as $wd) {
                    $cltparams["weekday{$dwn}"] = $wd;
                    $wdsql[] = "class_weekday like :weekday{$dwn}";
                    ++$dwn;
                }
                $cltermwhere .= ' AND ('.implode(' OR ', $wdsql).') ';
            }
            if (!empty($filters) && !empty($filters['agegroups'])) {
                $agsql = array();
                $agn = 0;
                foreach ($filters['agegroups'] as $wd) {
                    $cltparams["agegroup{$agn}"] = $wd;
                    $agsql[] = "class_agegroup_id=:agegroup{$agn}";
                    ++$agn;
                }
                $cltermwhere .= ' AND ('.implode(' OR ', $agsql).') ';
            }

            $lsql = "SELECT class_object_id,class_name,class_weekday,class_url,location_name, agegroup_name,class_size FROM tbl_class LEFT JOIN tbl_location ON class_location_id = location_object_id LEFT JOIN tbl_agegroup ON class_agegroup_id = agegroup_id  WHERE class_deleted IS NULL AND class_published = 1{$lwhere} AND agegroup_deleted IS NULL AND location_deleted IS NULL AND EXISTS (SELECT classterm_id FROM tbl_classterm WHERE classterm_class_id = class_object_id AND classterm_deleted IS NULL {$cltermwhere}) ORDER BY location_suburb, FIELD(class_weekday, 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY', 'SUNDAY'), class_start_time, agegroup_name";
            $classes = $DBobject->wrappedSql($lsql, array_merge($lparams, $cltparams));

      //get all information for students enrolled in class days after today
            foreach ($classes as &$c) {
                $sql_filters = " AND payment_id IN (SELECT order_payment_id FROM tbl_order LEFT JOIN tbl_status ON order_status_id = status_id WHERE order_deleted IS NULL AND status_type != 'partial' )";
                $psql_filters = " AND payment_id IN (SELECT order_payment_id FROM tbl_order LEFT JOIN tbl_status ON order_status_id = status_id WHERE order_deleted IS NULL AND status_type = 'partial' )";
                $psql = "SELECT payment_created,payment_discount,payment_additional,status_name, payment_total AS payment_charged_amount, user_gname, user_surname, user_email,address_telephone FROM tbl_payment LEFT JOIN tbl_order ON payment_id = order_payment_id LEFT JOIN tbl_status ON order_status_id = status_id LEFT JOIN tbl_user ON payment_user_id = user_id LEFT JOIN tbl_address ON payment_billing_address_id = address_id WHERE payment_deleted IS NULL AND order_deleted IS NULL AND status_deleted IS NULL AND user_deleted IS NULL AND EXISTS (SELECT * FROM tbl_enrolment WHERE enrolment_deleted IS NULL AND enrolment_published = '1' AND enrolment_payment_id = payment_id AND enrolment_class_object_id = :class_id AND enrolment_term_object_id = :term_id) {$sql_filters} ORDER BY payment_created DESC, status_name,user_gname,user_surname";
                $payments = $DBobject->wrappedSql($psql, array('class_id' => $c['class_object_id'], 'term_id' => $t['term_object_id']));
                if (empty($payments)) {
                    $payments = array();
                }
                $psql2 = "SELECT payment_created,payment_discount,payment_additional,status_name, payment_charged_amount, user_gname, user_surname, user_email,address_telephone FROM tbl_payment LEFT JOIN tbl_order ON payment_id = order_payment_id LEFT JOIN tbl_status ON order_status_id = status_id LEFT JOIN tbl_user ON payment_user_id = user_id LEFT JOIN tbl_address ON payment_billing_address_id = address_id WHERE payment_deleted IS NULL AND order_deleted IS NULL AND status_deleted IS NULL AND user_deleted IS NULL AND EXISTS (SELECT * FROM tbl_enrolment WHERE enrolment_deleted IS NULL AND enrolment_published = '1' AND enrolment_payment_id = payment_id AND enrolment_class_object_id = :class_id AND enrolment_term_object_id = :term_id) {$psql_filters} ORDER BY payment_created DESC, status_name,user_gname,user_surname";
                $payments2 = $DBobject->wrappedSql($psql2, array('class_id' => $c['class_object_id'], 'term_id' => $t['term_object_id']));
                if (empty($payments2)) {
                    $payments2 = array();
                }
                if (!empty($payments)) {
                    $c['payments'] = array_merge($payments, $payments2);
                }
            }
            if (!empty($classes)) {
                $l['classes'] = $classes;
            }
        }
        if (!empty($locations)) {
            $t['locations'] = $locations;
        }
    }

    return $termlist;
}

/**
 * Valid Filters:.
 *
 *    [weekdays] = this is the day of the week (1=Monday, 2=Tuesday, 3=Wednesday, 4=Thursday, 5=Friday, 6=Saturday, 7=Sunday
 *    [agegroups] = age group id
 *    [time] = time of day in 24hr
 *
 * @param string $id
 * @param array  $terms     An array of the term ids to list
 * @param array  $locations An array of the location ids to list
 * @param int    $week      The week number representing a particular week of the term
 * @param array  $filters
 */
function dailypaymentlist2($id = null, $terms = array(), $locations = array(), $filters = array(), $enrolmenttype = array())
{
    global $DBobject;

    $fparams = array();
    if (!empty($_SESSION['user']['admin']['franchisee'])) {
        $n = 1;
        $tmpsql = array();
        foreach ($_SESSION['user']['admin']['franchisee'] as $m) {
            $tmpsql[] = "location_franchisee_id = :franchisee{$n}";
            $fparams[":franchisee{$n}"] = $m;
            ++$n;
        }
        $sql_franchisee = ' AND ('.implode(' OR ', $tmpsql).') ';
    } else {
        if (!empty($filters) && !empty($filters['franchisee'])) {
            $n = 1;
            $tmpsql = array();
            foreach ($filters['franchisee'] as $f) {
                if (empty($f)) {
                    continue;
                }
                $tmpsql[] = "location_franchisee_id = :franchisee{$n}";
                $fparams[":franchisee{$n}"] = $f;
                ++$n;
            }
            if (!empty($tmpsql)) {
                $sql_franchisee = ' AND ('.implode(' OR ', $tmpsql).') ';
            }
        }
    }

  //This sql gets the term start day as the Sunday begining the week of the selected date SELECT DAYNAME(term_start_date), DAYOFWEEK(term_start_date),SUBDATE(term_start_date, (DAYOFWEEK(term_start_date)-1)),term_start_date FROM tbl_term
    if (empty($terms)) {
        $tparams = $fparams;
        $startdatesql = 'AND term_start_date <= NOW()';
        if (!empty($filters) && !empty($filters['start_date'])) {
            $startdatesql = '';
        }
        $enddatesql = 'AND term_end_date >= NOW()';
        if (!empty($filters) && !empty($filters['end_date'])) {
            $tparams['end_date'] = date('Y-m-d', strtotime(str_replace('/', '-', $filters['end_date'])));
            $enddatesql = ' AND DATE(term_created) <= :end_date ';
        }
        $tsql = "SELECT term_object_id FROM tbl_term WHERE term_deleted IS NULL {$startdatesql}{$enddatesql} AND term_published = 1 AND term_id IN (SELECT DISTINCT(classterm_term_id) FROM tbl_classterm LEFT JOIN tbl_class ON classterm_class_id = class_object_id WHERE class_deleted IS NULL AND classterm_deleted IS NULL AND classterm_published = 1 AND class_location_id IN (SELECT location_object_id FROM tbl_location WHERE location_deleted IS NULL {$sql_franchisee})) ORDER BY term_start_date DESC";
        $res = $DBobject->wrappedSql($tsql, $tparams);
        foreach ($res as $r) {
            $terms[] = $r['term_object_id'];
        }
        if (empty($terms)) {
            return array();
        }
    }
    $cltparams = array();
  //Get week numbers for all terms or assign static week number if selected
    $n = 1;
    $termlist = array();
    foreach ($terms as $id) {
        $wsql = 'SELECT term_name,term_region, term_id, term_object_id, SUBDATE(term_start_date, (DAYOFWEEK(term_start_date)-1)) AS firstsunday, '.(empty($week) ? 'IF(term_start_date < NOW(),CEIL(DATEDIFF(IF(term_end_date>NOW(),NOW(),term_start_date),SUBDATE(term_start_date, (DAYOFWEEK(term_start_date)-1)))/7),1)' : "{$week}").' AS weekno FROM tbl_term WHERE term_deleted IS NULL AND term_published = 1 AND term_object_id = :term_id';
        $tres = $DBobject->wrappedSql($wsql, array('term_id' => $id));
        $termlist[] = $tres[0];
        ++$n;
    }

  //Get Class record to show all classes for the selected term
    $lparams = array();
    $lids = array();
    $lwhere = '';
    $ln = 0;
    if (!empty($locations)) {
        foreach ($locations as $id) {
            $lids[] = "class_location_id = :local_id{$ln}";
            $lparams["local_id{$ln}"] = $id;
            ++$ln;
        }
        $lwhere = ' AND ('.implode(' OR ', $lids).')';
    }

  //Filter by region
    if (!empty($filters) && !empty($filters['regions'])) {
        $rdsql = array();
        $nr = 0;
        foreach ($filters['regions'] as $rg) {
            $fparams["region{$nr}"] = $rg;
            $rdsql[] = "location_region_id=:region{$nr}";
            ++$nr;
        }
        $sql_region = ' AND ('.implode(' OR ', $rdsql).') ';
    }

    foreach ($termlist as &$t) {
        $hasPayment = false;
        $locsql = "SELECT tbl_location.* FROM tbl_location LEFT JOIN tbl_class ON class_location_id = location_object_id LEFT JOIN tbl_classterm ON classterm_class_id = class_object_id  WHERE location_deleted IS NULL AND location_published = 1 {$sql_franchisee}{$sql_region} AND class_deleted IS NULL AND class_published = 1 AND classterm_deleted IS NULL AND classterm_published = 1 AND classterm_term_id = :term_id{$lwhere} GROUP BY location_object_id ORDER BY location_state, location_suburb";
        $locations = $DBobject->wrappedSql($locsql, array_merge($lparams, array(':term_id' => $t['term_id']), $fparams));
        foreach ($locations as &$l) {
            $cltermwhere = ' AND classterm_term_id = :term_id AND class_location_id = :loc_id';
            $cltparams['term_id'] = $t['term_id'];
            $cltparams['loc_id'] = $l['location_object_id'];

            if (!empty($filters) && !empty($filters['weekdays'])) {
                $wdsql = array();
                $dwn = 0;
                foreach ($filters['weekdays'] as $wd) {
                    $cltparams["weekday{$dwn}"] = $wd;
                    $wdsql[] = "class_weekday like :weekday{$dwn}";
                    ++$dwn;
                }
                $cltermwhere .= ' AND ('.implode(' OR ', $wdsql).') ';
            }
            if (!empty($filters) && !empty($filters['agegroups'])) {
                $agsql = array();
                $agn = 0;
                foreach ($filters['agegroups'] as $wd) {
                    $cltparams["agegroup{$agn}"] = $wd;
                    $agsql[] = "class_agegroup_id=:agegroup{$agn}";
                    ++$agn;
                }
                $cltermwhere .= ' AND ('.implode(' OR ', $agsql).') ';
            }

      //Date range
            $payparams = array();
            $paymwhere = '';
            if (!empty($filters) && !empty($filters['start_date'])) {
                $agsql = array();
                $payparams['start_date'] = date('Y-m-d', strtotime(str_replace('/', '-', $filters['start_date'])));
                $paymwhere .= ' AND DATE(order_created) >= :start_date ';
            }
            if (!empty($filters) && !empty($filters['end_date'])) {
                $agsql = array();
                $payparams['end_date'] = date('Y-m-d', strtotime(str_replace('/', '-', $filters['end_date'])));
                $paymwhere .= ' AND DATE(order_created) <= :end_date ';
            }

            $lsql = "SELECT class_object_id,class_name,class_weekday,class_url,location_name, agegroup_name,class_size FROM tbl_class LEFT JOIN tbl_location ON class_location_id = location_object_id LEFT JOIN tbl_agegroup ON class_agegroup_id = agegroup_id LEFT JOIN tbl_classterm ON classterm_class_id = class_object_id WHERE class_deleted IS NULL AND class_published = 1{$lwhere} AND agegroup_deleted IS NULL AND location_deleted IS NULL AND classterm_published = 1 AND classterm_deleted IS NULL {$cltermwhere} ORDER BY location_suburb, FIELD(class_weekday, 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY', 'SUNDAY'), class_start_time, agegroup_name";
            $classes = $DBobject->wrappedSql($lsql, array_merge($lparams, $cltparams));

      //get all information for students enrolled in class days after today
            foreach ($classes as &$c) {
                $sql_filters = " AND payment_id IN (SELECT order_payment_id FROM tbl_order LEFT JOIN tbl_status ON order_status_id = status_id WHERE order_deleted IS NULL AND status_type != 'partial' AND status_category = 'approved' )";
                    /* FOR PARTIAL PAYMENTS
                    $psql_filters=" AND payment_id IN (SELECT order_payment_id FROM tbl_order LEFT JOIN tbl_status ON order_status_id = status_id WHERE order_deleted IS NULL AND status_type = 'partial' AND status_category = 'approved' )";
                    */
                    $psql = "SELECT payment_id, payment_cart_id,order_created, payment_created,(payment_discount/enrolment_count) AS payment_discount,(payment_additional/enrolment_count) AS payment_additional,status_name, payment_total, payment_total AS payment_charged_amount, user_gname, user_surname, user_email,address_telephone,enrolment_count FROM tbl_payment LEFT JOIN tbl_order ON payment_id = order_payment_id LEFT JOIN tbl_status ON order_status_id = status_id LEFT JOIN tbl_user ON payment_user_id = user_id LEFT JOIN tbl_address ON payment_billing_address_id = address_id LEFT JOIN (SELECT enrolment_payment_id, COUNT(DISTINCT (CONCAT(enrolment_class_object_id,'-',enrolment_term_object_id))) AS enrolment_count  FROM tbl_enrolment WHERE (enrolment_type = 'S' OR enrolment_type = 'MV') AND enrolment_published = '1' GROUP BY enrolment_payment_id) AS tbl_enrolment ON enrolment_payment_id = payment_id LEFT JOIN (SELECT DISTINCT(enrolment_payment_id) AS pid_tmp FROM tbl_enrolment WHERE enrolment_deleted IS NULL AND enrolment_published = '1' AND enrolment_class_object_id = :class_id AND enrolment_term_object_id = :term_id AND (enrolment_type = 'S' OR enrolment_type = 'MV')) AS tmp_pay ON pid_tmp = payment_id WHERE pid_tmp IS NOT NULL AND payment_deleted IS NULL AND order_deleted IS NULL AND status_deleted IS NULL AND user_deleted IS NULL   {$paymwhere} {$sql_filters} ORDER BY order_created DESC, status_name,user_gname,user_surname";

                $payments = $DBobject->wrappedSql($psql, array_merge(array('class_id' => $c['class_object_id'], 'term_id' => $t['term_object_id']), $payparams));
                if (empty($payments)) {
                    $payments = array();
                }

                    //if($_SERVER['REMOTE_ADDR']=='150.101.230.130') { var_dump($psql);var_dump(array_merge(array("class_id"=>$c['class_object_id'],"term_id"=>$t['term_object_id']),$payparams)); die();}
                    //if($_SERVER['REMOTE_ADDR']=='150.101.230.130') { var_dump($payments);die();}

                    /* FOR PARTIAL PAYMENTS
                    $psql2 ="SELECT payment_id, payment_cart_id,order_created, payment_created,(payment_discount/enrolment_count) AS payment_discount,(payment_additional/enrolment_count) AS payment_additional,status_name, payment_total, payment_charged_amount, user_gname, user_surname, user_email,address_telephone,enrolment_count FROM tbl_payment LEFT JOIN tbl_order ON payment_id = order_payment_id LEFT JOIN tbl_status ON order_status_id = status_id LEFT JOIN tbl_user ON payment_user_id = user_id LEFT JOIN tbl_address ON payment_billing_address_id = address_id LEFT JOIN (SELECT enrolment_payment_id, COUNT(DISTINCT (CONCAT(enrolment_class_object_id,'-',enrolment_term_object_id))) AS enrolment_count  FROM tbl_enrolment WHERE (enrolment_type = 'S' OR enrolment_type = 'MV') AND enrolment_published = '1' GROUP BY enrolment_payment_id) AS tbl_enrolment ON enrolment_payment_id = payment_id LEFT JOIN (SELECT DISTINCT(enrolment_payment_id) AS pid_tmp FROM tbl_enrolment WHERE enrolment_deleted IS NULL AND enrolment_published = '1' AND enrolment_class_object_id = :class_id AND enrolment_term_object_id = :term_id AND (enrolment_type = 'S' OR enrolment_type = 'MV')) AS tmp_pay ON pid_tmp = payment_id WHERE pid_tmp IS NOT NULL AND payment_deleted IS NULL AND order_deleted IS NULL AND status_deleted IS NULL AND user_deleted IS NULL   {$paymwhere} {$psql_filters} ORDER BY order_created DESC, status_name,user_gname,user_surname";
                    $payments2 = $DBobject->wrappedSql($psql2,array_merge(array("class_id"=>$c['class_object_id'],"term_id"=>$t['term_object_id']),$payparams));
                    */
                if (empty($payments2)) {
                    $payments2 = array();
                }

                foreach ($payments as &$p) {
                    //SELECT original student count for CLASS_ID
                    $sql_o = "SELECT enrolment_class_object_id, COUNT(DISTINCT(CONCAT(enrolment_student_id, enrolment_term_object_id,enrolment_class_object_id, enrolment_payment_id))) AS cnt FROM tbl_enrolment LEFT JOIN tbl_student ON enrolment_student_id = student_id WHERE enrolment_class_object_id = :class_id AND enrolment_term_object_id = :term_id AND enrolment_payment_id = :payment_id AND enrolment_published = 1 AND enrolment_type = 'S' GROUP BY enrolment_class_object_id";
                    $o_res = $DBobject->wrappedSql($sql_o, array('payment_id' => $p['payment_id'], 'class_id' => $c['class_object_id'], 'term_id' => $t['term_object_id']));

                //SELECT moved student count for CLASS_ID grouped into original CLASS_IDs
                      $sql_m = "SELECT enrolment_class_object_id, COUNT(DISTINCT(CONCAT(enrolment_student_id,enrolment_term_object_id,enrolment_class_object_id,enrolment_payment_id))) AS cnt FROM tbl_enrolment LEFT JOIN tbl_student ON enrolment_student_id = student_id WHERE enrolment_term_object_id = :term_id AND enrolment_payment_id = :payment_id AND enrolment_published = 0 AND enrolment_type = 'S' AND enrolment_student_id IN (SELECT enrolment_student_id FROM tbl_enrolment WHERE enrolment_class_object_id = :class_id AND enrolment_term_object_id = :term_id AND enrolment_payment_id = :payment_id AND enrolment_type = 'MV') GROUP BY enrolment_class_object_id ";
                          $m_res = $DBobject->wrappedSql($sql_m, array('payment_id' => $p['payment_id'], 'class_id' => $c['class_object_id'], 'term_id' => $t['term_object_id']));

                      //SELECT PRICE from associated CARTITEM
                      $total = 0.0;
                          $tsql = 'SELECT cartitem_product_price FROM tbl_cartitem LEFT JOIN tbl_classterm ON cartitem_product_id = classterm_id LEFT JOIN tbl_term ON classterm_term_id = term_id WHERE term_object_id = :term_id AND classterm_class_id = :class_id AND classterm_deleted IS NULL AND cartitem_cart_id = :cart_id AND cartitem_class_flag = 1 AND cartitem_deleted IS NULL';
                          $tpres = $DBobject->wrappedSql($tsql, array('cart_id' => $p['payment_cart_id'], 'class_id' => $o_res[0]['enrolment_class_object_id'], 'term_id' => $t['term_object_id']));

                      //Multiply price by quantity
                      $total = $total + (floatval($tpres[0]['cartitem_product_price']) * floatval($o_res[0]['cnt']));
                    foreach ($m_res as $row) {
                        $tsql = 'SELECT cartitem_product_price FROM tbl_cartitem LEFT JOIN tbl_classterm ON cartitem_product_id = classterm_id LEFT JOIN tbl_term ON classterm_term_id = term_id WHERE term_object_id = :term_id AND classterm_class_id = :class_id AND classterm_deleted IS NULL AND cartitem_cart_id = :cart_id AND cartitem_class_flag = 1 AND cartitem_deleted IS NULL';
                        $tpres = $DBobject->wrappedSql($tsql, array('cart_id' => $p['payment_cart_id'], 'class_id' => $row['enrolment_class_object_id'], 'term_id' => $t['term_object_id']));
                        //Multiply price by quantity
                        $total = $total + (floatval($tpres[0]['cartitem_product_price']) * floatval($row['cnt']));
                    }

                          $tsql = 'SELECT SUM(cartitem_product_price*cartitem_quantity) AS total FROM tbl_cartitem WHERE cartitem_cart_id = :cart_id AND cartitem_class_flag = 0 AND cartitem_deleted IS NULL';
                          $tprodres = $DBobject->wrappedSql($tsql, array('cart_id' => $p['payment_cart_id']));
                          $p['classes'] = $total;
                          $p['merchandise'] = $tprodres[0]['total'] / $p['enrolment_count'];
                }
                    /* FOR PARTIAL PAYMENTS
                    foreach($payments2 as &$p){
                      //SELECT original student count for CLASS_ID
                      $sql_o = "SELECT enrolment_class_object_id, COUNT(DISTINCT(CONCAT(enrolment_student_id, enrolment_term_object_id,enrolment_class_object_id, enrolment_payment_id))) AS cnt FROM tbl_enrolment LEFT JOIN tbl_student ON enrolment_student_id = student_id WHERE enrolment_class_object_id = :class_id AND enrolment_term_object_id = :term_id AND enrolment_payment_id = :payment_id AND enrolment_published = 1 AND enrolment_type = 'S' GROUP BY enrolment_class_object_id";
                      $o_res = $DBobject->wrappedSql($sql_o, array('payment_id'=>$p['payment_id'],'class_id'=>$c['class_object_id'],'term_id'=>$t['term_object_id']));
          
                      //SELECT moved student count for CLASS_ID grouped into original CLASS_IDs
                      $sql_m = "SELECT enrolment_class_object_id, COUNT(DISTINCT(CONCAT(enrolment_student_id,enrolment_term_object_id,enrolment_class_object_id,enrolment_payment_id))) AS cnt FROM tbl_enrolment LEFT JOIN tbl_student ON enrolment_student_id = student_id WHERE enrolment_term_object_id = :term_id AND enrolment_payment_id = :payment_id AND enrolment_published = 0 AND enrolment_type = 'S' AND enrolment_student_id IN (SELECT enrolment_student_id FROM tbl_enrolment WHERE enrolment_class_object_id = :class_id AND enrolment_term_object_id = :term_id AND enrolment_payment_id = :payment_id AND enrolment_type = 'MV') GROUP BY enrolment_class_object_id ";
                      $m_res = $DBobject->wrappedSql($sql_m, array('payment_id'=>$p['payment_id'],'class_id'=>$c['class_object_id'],'term_id'=>$t['term_object_id']));

                      //SELECT PRICE from associated CARTITEM
                      $total = 0;
                      $tsql = "SELECT cartitem_product_price FROM tbl_cartitem LEFT JOIN tbl_classterm ON cartitem_product_id = classterm_id WHERE classterm_class_id = :class_id AND classterm_deleted IS NULL AND cartitem_cart_id = :cart_id AND cartitem_class_flag = 1 AND cartitem_deleted IS NULL";
                      $tpres = $DBobject->wrappedSql($tsql, array('cart_id'=>$p['payment_cart_id'],'class_id'=>$o_res[0]['enrolment_class_object_id']));
                      //Multiply price by quantity
                      $total = $total + (floatval($tpres[0]['cartitem_product_price']) * floatval($o_res[0]['cnt']));
                      foreach($m_res as $row){
                        $tsql = "SELECT cartitem_product_price FROM tbl_cartitem LEFT JOIN tbl_classterm ON cartitem_product_id = classterm_id WHERE classterm_class_id = :class_id AND classterm_deleted IS NULL AND cartitem_cart_id = :cart_id AND cartitem_class_flag = 1 AND cartitem_deleted IS NULL";
                        $tpres = $DBobject->wrappedSql($tsql, array('cart_id'=>$p['payment_cart_id'],'class_id'=>$row['enrolment_class_object_id']));
                        //Multiply price by quantity
                        $total = $total + (floatval($tpres[0]['cartitem_product_price']) * floatval($row['cnt']));
                      }

                      $tsql = "SELECT SUM(cartitem_product_price*cartitem_quantity) AS total FROM tbl_cartitem WHERE cartitem_cart_id = :cart_id AND cartitem_class_flag = 0 AND cartitem_deleted IS NULL";
                      $tprodres = $DBobject->wrappedSql($tsql, array('cart_id'=>$p['payment_cart_id']));
                      $p['classes'] = $total;
                      $p['merchandise'] = $tprodres[0]['total']/$p['enrolment_count'];
                    }
                    */
                if (!empty($payments) || !empty($payments2)) {
                      $c['payments'] = array_merge($payments, $payments2);
                      $hasPayment = true;
                }
            }
            if (!empty($classes)) {
                $l['classes'] = $classes;
            }
        }
        if (!empty($locations)) {
            $t['locations'] = $locations;
        }
        if (!$hasPayment) {
            $t = null;
            unset($t);
        }
    }

    return $termlist;
}

/**
 * Valid Filters:.
 *
 *    [weekdays] = this is the day of the week (1=Monday, 2=Tuesday, 3=Wednesday, 4=Thursday, 5=Friday, 6=Saturday, 7=Sunday
 *    [agegroups] = age group id
 *    [time] = time of day in 24hr
 *
 * @param string $id
 * @param array  $terms     An array of the term ids to list
 * @param array  $locations An array of the location ids to list
 * @param int    $week      The week number representing a particular week of the term
 * @param array  $filters
 */
function paymentlist($id = null, $terms = array(), $locations = array(), $filters = array(), $enrolmenttype = array())
{
    global $DBobject;
    $params = array();
    $lwhere = '';
    if (!empty($_SESSION['user']['admin']['franchisee'])) {
        $n = 1;
        $tmpsql = array();
        foreach ($_SESSION['user']['admin']['franchisee'] as $m) {
            $tmpsql[] = "location_franchisee_id = :franchisee{$n}";
            $params[":franchisee{$n}"] = $m;
            ++$n;
        }
        $lwhere .= ' AND ('.implode(' OR ', $tmpsql).') ';
    }

    $tids = array();
    if (!empty($terms)) {
        $n = 1;
        foreach ($terms as $id) {
            $tids[] = "enrolment_term_object_id = :term_id{$n}";
            $params["term_id{$n}"] = $id;
            ++$n;
        }
        $lwhere .= ' AND ('.implode(' OR ', $tids).')';
    }
    $lids = array();
    if (!empty($locations)) {
        $n = 1;
        foreach ($locations as $id) {
            $lids[] = "class_location_id = :local_id{$n}";
            $params["local_id{$n}"] = $id;
            ++$n;
        }
        $lwhere .= ' AND ('.implode(' OR ', $lids).')';
    }
    if (!empty($filters) && !empty($filters['regions'])) {
        $rdsql = array();
        $nr = 0;
        foreach ($filters['regions'] as $rg) {
            $rdsql[] = "location_region_id=:region{$nr}";
            $params["region{$nr}"] = $rg;
            ++$nr;
        }
        $lwhere .= ' AND ('.implode(' OR ', $rdsql).') ';
    }
    if (!empty($filters) && !empty($filters['weekdays'])) {
        $wdsql = array();
        $dwn = 0;
        foreach ($filters['weekdays'] as $wd) {
            $params["weekday{$dwn}"] = $wd;
            $wdsql[] = "class_weekday like :weekday{$dwn}";
            ++$dwn;
        }
        $lwhere .= ' AND ('.implode(' OR ', $wdsql).') ';
    }
    if (!empty($filters) && !empty($filters['agegroups'])) {
        $agsql = array();
        $agn = 0;
        foreach ($filters['agegroups'] as $wd) {
            $params["agegroup{$agn}"] = $wd;
            $agsql[] = "class_agegroup_id=:agegroup{$agn}";
            ++$agn;
        }
        $lwhere .= ' AND ('.implode(' OR ', $agsql).') ';
    }
    $stn = 0;
    $stsql = array();
    if (!empty($enrolmenttype)) {
        foreach ($enrolmenttype as $s) {
            $params["enrltype{$stn}"] = $s;
            $stsql[] = "enrolment_type=:enrltype{$stn}";
            ++$stn;
        }
        $lwhere .= ' AND ('.implode(' OR ', $stsql).') ';
    }

    $sql_enrolments = '';
  /* MOVED DOWN
    if(!empty($lwhere)){
    $sql_enrolments = " AND payment_id IN (SELECT DISTINCT(enrolment_payment_id) FROM tbl_enrolment LEFT JOIN tbl_class ON enrolment_class_object_id = class_object_id LEFT JOIN tbl_location ON class_location_id = location_object_id WHERE location_deleted IS NULL AND class_deleted IS NULL AND enrolment_deleted IS NULL {$lwhere} AND (enrolment_published = '1' OR 0=(SELECT COUNT(en.enrolment_class_object_id) FROM tbl_enrolment AS en WHERE en.enrolment_student_id = enrolment_student_id AND en.enrolment_payment_id = enrolment_payment_id AND en.enrolment_published = '1') )) ";
  } */

  //Issue raised. Cancelled payments will not be accurate if they have moved class and then the final record is also cancelled.

  //This sql gets the term start day as the Sunday begining the week of the selected date SELECT DAYNAME(term_start_date), DAYOFWEEK(term_start_date),SUBDATE(term_start_date, (DAYOFWEEK(term_start_date)-1)),term_start_date FROM tbl_term
    $sql_filters = " AND payment_id IN (SELECT order_payment_id FROM tbl_order LEFT JOIN tbl_status ON order_status_id = status_id WHERE order_deleted IS NULL AND status_type != 'partial' )";
    $psql_filters = " AND payment_id IN (SELECT order_payment_id FROM tbl_order LEFT JOIN tbl_status ON order_status_id = status_id WHERE order_deleted IS NULL AND status_type = 'partial' )";

    $tsql = "SELECT SUM(payment_total) AS total,payment_status FROM tbl_payment WHERE payment_deleted IS NULL {$sql_enrolments} {$sql_filters} GROUP BY payment_status";
    if (!empty($lwhere)) {
        $subtsql = "SELECT payment_total,payment_status,payment_id FROM tbl_payment LEFT JOIN tbl_enrolment ON payment_id = enrolment_payment_id LEFT JOIN tbl_class ON enrolment_class_object_id = class_object_id LEFT JOIN tbl_location ON class_location_id = location_object_id WHERE payment_deleted IS NULL AND location_deleted IS NULL AND class_deleted IS NULL AND enrolment_deleted IS NULL AND (enrolment_published = '1' OR 0=(SELECT COUNT(en.enrolment_class_object_id) FROM tbl_enrolment AS en WHERE en.enrolment_student_id = enrolment_student_id AND en.enrolment_payment_id = enrolment_payment_id AND en.enrolment_published = '1')) {$lwhere} {$sql_filters} GROUP BY payment_id";
        $tsql = "SELECT SUM(payment_total) AS total,payment_status FROM ({$subtsql}) AS TMP GROUP BY payment_status";
    }

    $res = $DBobject->wrappedSql($tsql, $params);
    if (empty($res)) {
        $res = array();
    }
    $tsql2 = "SELECT SUM(payment_charged_amount) AS total,payment_status FROM tbl_payment WHERE payment_deleted IS NULL {$sql_enrolments} {$psql_filters} GROUP BY payment_status";
    if (!empty($lwhere)) {
        $subtsql = "SELECT payment_charged_amount,payment_status,payment_id FROM tbl_payment LEFT JOIN tbl_enrolment ON payment_id = enrolment_payment_id LEFT JOIN tbl_class ON enrolment_class_object_id = class_object_id LEFT JOIN tbl_location ON class_location_id = location_object_id WHERE payment_deleted IS NULL AND location_deleted IS NULL AND class_deleted IS NULL AND enrolment_deleted IS NULL AND (enrolment_published = '1' OR 0=(SELECT COUNT(en.enrolment_class_object_id) FROM tbl_enrolment AS en WHERE en.enrolment_student_id = enrolment_student_id AND en.enrolment_payment_id = enrolment_payment_id AND en.enrolment_published = '1')) {$lwhere} {$sql_filters} GROUP BY payment_id";
        $tsql2 = "SELECT SUM(payment_charged_amount) AS total,payment_status FROM ({$subtsql}) AS TMP GROUP BY payment_status";
    }
    $res2 = $DBobject->wrappedSql($tsql2, $params);
    if (empty($res2)) {
        $res2 = array();
    }

    $data = array();
    foreach ($res as $r) {
        if (!empty($data[$r['payment_status']])) {
            $data[$r['payment_status']] = $data[$r['payment_status']] + $r;
        } else {
            $data[$r['payment_status']] = $r;
        }
    }
    foreach ($res2 as $r) {
        if (!empty($data[$r['payment_status']])) {
            $data[$r['payment_status']] = $data[$r['payment_status']] + $r;
        } else {
            $data[$r['payment_status']] = $r;
        }
    }

    return $data;
//     if(!empty($res)){
//       return $res;
//     }else{
//       return array();
//     }
}

/**
 * Valid Filters:.
 *
 *    [weekdays] = this is the day of the week (1=Monday, 2=Tuesday, 3=Wednesday, 4=Thursday, 5=Friday, 6=Saturday, 7=Sunday
 *    [agegroups] = age group id
 *    [time] = time of day in 24hr
 *
 * @param string $id
 * @param array  $terms     An array of the term ids to list
 * @param array  $locations An array of the location ids to list
 * @param int    $week      The week number representing a particular week of the term
 * @param array  $filters
 */
function reenrolmentlist($id = null, $terms = array(), $locations = array(), $filters = array())
{
    global $DBobject;

    $fparams = array();
    if (!empty($_SESSION['user']['admin']['franchisee'])) {
        $n = 1;
        $tmpsql = array();
        foreach ($_SESSION['user']['admin']['franchisee'] as $m) {
            $tmpsql[] = "location_franchisee_id = :franchisee{$n}";
            $fparams[":franchisee{$n}"] = $m;
            ++$n;
        }
        $sql_franchisee = ' AND ('.implode(' OR ', $tmpsql).') ';
    }

  //This sql gets the term start day as the Sunday begining the week of the selected date SELECT DAYNAME(term_start_date), DAYOFWEEK(term_start_date),SUBDATE(term_start_date, (DAYOFWEEK(term_start_date)-1)),term_start_date FROM tbl_term
    if (empty($terms)) {
        //     $tsql = "SELECT term_object_id FROM tbl_term WHERE term_deleted IS NULL AND term_published = 1 AND term_start_date <= NOW() AND term_end_date >= NOW()";
    //     $res = $DBobject->wrappedSql($tsql);
        $tsql = "SELECT term_object_id FROM tbl_term WHERE term_deleted IS NULL AND term_published = 1 AND term_start_date <= NOW() AND term_end_date >= NOW() AND term_id IN (SELECT DISTINCT(classterm_term_id) FROM tbl_classterm LEFT JOIN tbl_class ON classterm_class_id = class_object_id WHERE class_deleted IS NULL AND classterm_deleted IS NULL AND classterm_published = 1 AND class_location_id IN (SELECT location_object_id FROM tbl_location WHERE location_deleted IS NULL {$sql_franchisee})) ORDER BY term_start_date DESC";
        $res = $DBobject->wrappedSql($tsql, $fparams);
        foreach ($res as $r) {
            $terms[] = $r['term_object_id'];
        }
        if (empty($terms)) {
            return array();
        }
    }
    $cltparams = array();
  //Get week numbers for all terms or assign static week number if selected
    $n = 1;
    $termlist = array();
    foreach ($terms as $id) {
        $wsql = 'SELECT term_termcategory_id, term_region,term_name, term_start_date, term_id, term_object_id, SUBDATE(term_start_date, (DAYOFWEEK(term_start_date)-1)) AS firstsunday, '.(empty($week) ? 'IF(term_start_date < NOW(),CEIL(DATEDIFF(IF(term_end_date>NOW(),NOW(),term_start_date),SUBDATE(term_start_date, (DAYOFWEEK(term_start_date)-1)))/7),1)' : "{$week}").' AS weekno FROM tbl_term WHERE term_deleted IS NULL AND term_published = 1 AND term_object_id = :term_id';
        $tres = $DBobject->wrappedSql($wsql, array('term_id' => $id));
        $termlist[] = $tres[0];
        ++$n;
    }

  //Get Class record to show all classes for the selected term
    $lparams = array();
    $lids = array();
    $lwhere = '';
    $ln = 0;
    if (!empty($locations)) {
        foreach ($locations as $id) {
            $lids[] = "class_location_id = :local_id{$ln}";
            $lparams["local_id{$ln}"] = $id;
            ++$ln;
        }
        $lwhere = ' AND ('.implode(' OR ', $lids).')';
    }

  //Filter by region
    if (!empty($filters) && !empty($filters['regions'])) {
        $rdsql = array();
        $nr = 0;
        foreach ($filters['regions'] as $rg) {
            $fparams["region{$nr}"] = $rg;
            $rdsql[] = "location_region_id=:region{$nr}";
            ++$nr;
        }
        $sql_region = ' AND ('.implode(' OR ', $rdsql).') ';
    }

    foreach ($termlist as &$t) {
        //   	$tmsql = "SELECT * FROM tbl_term WHERE term_deleted IS NULL AND term_published = 1 AND term_termcategory_id < :termcat AND term_region = :region AND term_start_date < :start ORDER BY term_termcategory_id DESC LIMIT 1";
//   	$preterm = $DBobject->wrappedSql($tmsql,array("termcat"=>($t["term_termcategory_id"]==1?5:$t["term_termcategory_id"]),"region"=>$t["term_region"],"start"=>$t["term_start_date"])); //NEED TO ADJUST START DATE BY 1 year if term id = 1
        $sqlyear = "SELECT * FROM tbl_term WHERE term_published = 1 AND term_termcategory_id != :term AND term_start_date <:start AND term_region = :region ORDER BY DATE_FORMAT(term_start_date,'%Y%m%d') DESC LIMIT 1";
        $preterm = $DBobject->wrappedSql($sqlyear, array('term' => $t['term_termcategory_id'], 'start' => $t['term_start_date'], 'region' => $t['term_region']));

        $locsql = "SELECT * FROM tbl_location WHERE location_deleted IS NULL AND location_published = 1 {$sql_franchisee}{$sql_region} AND EXISTS (SELECT classterm_id FROM tbl_classterm LEFT JOIN tbl_class ON classterm_class_id = class_object_id WHERE class_location_id = location_object_id AND class_deleted IS NULL AND class_published = 1 AND classterm_deleted IS NULL AND classterm_published = 1 AND (classterm_term_id = :term_id OR classterm_term_id = :pre_term_id){$lwhere}) ORDER BY location_state, location_suburb";
        $locations = $DBobject->wrappedSql($locsql, array_merge($lparams, array(':term_id' => $t['term_id'], ':pre_term_id' => $preterm[0]['term_id']), $fparams));
        foreach ($locations as &$l) {
            $cltermwhere = ' AND (classterm_term_id = :term_id OR classterm_term_id = :pre_term_id)AND class_location_id = :loc_id';
            $cltparams['term_id'] = $t['term_id'];
            $cltparams['pre_term_id'] = $preterm[0]['term_id'];
            $cltparams['loc_id'] = $l['location_object_id'];

            if (!empty($filters) && !empty($filters['weekdays'])) {
                $wdsql = array();
                $dwn = 0;
                foreach ($filters['weekdays'] as $wd) {
                    $cltparams["weekday{$dwn}"] = $wd;
                    $wdsql[] = "class_weekday like :weekday{$dwn}";
                    ++$dwn;
                }
                $cltermwhere .= ' AND ('.implode(' OR ', $wdsql).') ';
            }
            if (!empty($filters) && !empty($filters['agegroups'])) {
                $agsql = array();
                $agn = 0;
                foreach ($filters['agegroups'] as $wd) {
                    $cltparams["agegroup{$agn}"] = $wd;
                    $agsql[] = "class_agegroup_id=:agegroup{$agn}";
                    ++$agn;
                }
                $cltermwhere .= ' AND ('.implode(' OR ', $agsql).') ';
            }

            $lsql = "SELECT class_object_id,class_name,class_weekday,class_url,location_name, agegroup_name,class_size FROM tbl_class LEFT JOIN tbl_location ON class_location_id = location_object_id LEFT JOIN tbl_agegroup ON class_agegroup_id = agegroup_id  WHERE class_deleted IS NULL AND class_published = 1{$lwhere} AND agegroup_deleted IS NULL AND location_deleted IS NULL AND EXISTS (SELECT classterm_id FROM tbl_classterm WHERE classterm_class_id = class_object_id AND classterm_deleted IS NULL {$cltermwhere}) ORDER BY location_suburb, FIELD(class_weekday, 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY', 'SUNDAY'), class_start_time, agegroup_name";
            $classes = $DBobject->wrappedSql($lsql, array_merge($lparams, $cltparams));
      //get all information for students enrolled in class days after today
            foreach ($classes as &$c) {
                $curesql = "SELECT COUNT(DISTINCT(CONCAT(enrolment_student_id, enrolment_class_object_id))) AS enrolment FROM tbl_enrolment WHERE enrolment_deleted IS NULL AND enrolment_published = 1  AND enrolment_term_object_id = :term_id AND enrolment_class_object_id = :class_id AND (enrolment_type = 'S' OR enrolment_type = 'MV') ";
                $curenrls = $DBobject->wrappedSql($curesql, array('class_id' => $c['class_object_id'], 'term_id' => $t['term_object_id']));
                $c['reenrolment']['rate'] = 0;
                $c['reenrolment']['enrolled'] = $curenrls[0]['enrolment'];
                $c['reenrolment']['reenrolled'] = 0;
                $c['reenrolment']['lastenrolments'] = 0;

                if ($preterm) {
                    $esql = "SELECT COUNT(DISTINCT(CONCAT(enrolment_student_id, enrolment_class_object_id))) AS enrolment FROM tbl_enrolment WHERE enrolment_deleted IS NULL AND tbl_enrolment.enrolment_published = 1 AND enrolment_term_object_id = :pre_term_id AND enrolment_class_object_id = :class_id AND (enrolment_type = 'S' OR enrolment_type = 'MV') AND  EXISTS (
          SELECT tmp.enrolment_id FROM tbl_enrolment AS tmp WHERE tbl_enrolment.enrolment_student_id = tmp.enrolment_student_id AND tmp.enrolment_deleted IS NULL AND tmp.enrolment_published = 1 AND tmp.enrolment_term_object_id = :term_id)";
                    $enrls = $DBobject->wrappedSql($esql, array('class_id' => $c['class_object_id'], 'term_id' => $t['term_object_id'], 'pre_term_id' => $preterm[0]['term_object_id']));
                //if($_SERVER['REMOTE_ADDR']=='150.101.230.130') { var_dump($esql);var_dump(array("class_id"=>$c['class_object_id'],"term_id"=>$t['term_object_id'],"pre_term_id"=>$preterm[0]['term_object_id'])); die();}
                    $preesql = "SELECT COUNT(DISTINCT(CONCAT(enrolment_student_id, enrolment_class_object_id))) AS enrolment FROM tbl_enrolment WHERE enrolment_deleted IS NULL AND enrolment_published = 1  AND enrolment_term_object_id = :term_id AND enrolment_class_object_id = :class_id AND (enrolment_type = 'S' OR enrolment_type = 'MV')";
                    $preenrls = $DBobject->wrappedSql($preesql, array('class_id' => $c['class_object_id'], 'term_id' => $preterm[0]['term_object_id']));

                    try {
                        $c['reenrolment']['rate'] = number_format((intval($enrls[0]['enrolment']) / intval($preenrls[0]['enrolment'])) * 100, 2);
                    } catch (Exception $e) {
                    }

                    $c['reenrolment']['enrolled'] = $curenrls[0]['enrolment'];
                    $c['reenrolment']['reenrolled'] = $enrls[0]['enrolment'];
                    $c['reenrolment']['lastenrolments'] = $preenrls[0]['enrolment'];
                }
            }
            if (!empty($classes)) {
                $l['classes'] = $classes;
            }
        }
        if (!empty($locations)) {
            $t['locations'] = $locations;
        }
    }

    return $termlist;
}

/**
 * Valid Filters:.
 *
 *    [weekdays] = this is the day of the week (1=Monday, 2=Tuesday, 3=Wednesday, 4=Thursday, 5=Friday, 6=Saturday, 7=Sunday
 *    [agegroups] = age group id
 *    [time] = time of day in 24hr
 *
 * @param string $id
 * @param array  $terms     An array of the term ids to list
 * @param array  $locations An array of the location ids to list
 * @param int    $week      The week number representing a particular week of the term
 * @param array  $filters
 */
function trialconversionlist($id = null, $terms = array(), $locations = array(), $filters = array())
{
    global $DBobject;

    $fparams = array();
    if (!empty($_SESSION['user']['admin']['franchisee'])) {
        $n = 1;
        $tmpsql = array();
        foreach ($_SESSION['user']['admin']['franchisee'] as $m) {
            $tmpsql[] = "location_franchisee_id = :franchisee{$n}";
            $fparams[":franchisee{$n}"] = $m;
            ++$n;
        }
        $sql_franchisee = ' AND ('.implode(' OR ', $tmpsql).') ';
    }

  //This sql gets the term start day as the Sunday begining the week of the selected date SELECT DAYNAME(term_start_date), DAYOFWEEK(term_start_date),SUBDATE(term_start_date, (DAYOFWEEK(term_start_date)-1)),term_start_date FROM tbl_term
    if (empty($terms)) {
        //     $tsql = "SELECT term_object_id FROM tbl_term WHERE term_deleted IS NULL AND term_published = 1 AND term_start_date <= NOW() AND term_end_date >= NOW()";
    //     $res = $DBobject->wrappedSql($tsql);
        $tsql = "SELECT term_object_id FROM tbl_term WHERE term_deleted IS NULL AND term_published = 1 AND term_start_date <= NOW() AND term_end_date >= NOW() AND term_id IN (SELECT DISTINCT(classterm_term_id) FROM tbl_classterm LEFT JOIN tbl_class ON classterm_class_id = class_object_id WHERE class_deleted IS NULL AND classterm_deleted IS NULL AND classterm_published = 1 AND class_location_id IN (SELECT location_object_id FROM tbl_location WHERE location_deleted IS NULL {$sql_franchisee})) ORDER BY term_start_date DESC";
        $res = $DBobject->wrappedSql($tsql, $fparams);
        foreach ($res as $r) {
            $terms[] = $r['term_object_id'];
        }
        if (empty($terms)) {
            return array();
        }
    }
    $cltparams = array();
  //Get week numbers for all terms or assign static week number if selected
    $n = 1;
    $termlist = array();
    foreach ($terms as $id) {
        $wsql = 'SELECT term_termcategory_id, term_region,term_name, term_start_date, term_id, term_object_id, SUBDATE(term_start_date, (DAYOFWEEK(term_start_date)-1)) AS firstsunday, '.(empty($week) ? 'IF(term_start_date < NOW(),CEIL(DATEDIFF(IF(term_end_date>NOW(),NOW(),term_start_date),SUBDATE(term_start_date, (DAYOFWEEK(term_start_date)-1)))/7),1)' : "{$week}").' AS weekno FROM tbl_term WHERE term_deleted IS NULL AND term_published = 1 AND term_object_id = :term_id';
        $tres = $DBobject->wrappedSql($wsql, array('term_id' => $id));
        $termlist[] = $tres[0];
        ++$n;
    }

  //Get Class record to show all classes for the selected term
    $lparams = array();
    $lids = array();
    $lwhere = '';
    $ln = 0;
    if (!empty($locations)) {
        foreach ($locations as $id) {
            $lids[] = "class_location_id = :local_id{$ln}";
            $lparams["local_id{$ln}"] = $id;
            ++$ln;
        }
        $lwhere = ' AND ('.implode(' OR ', $lids).')';
    }

  //Filter by region
    if (!empty($filters) && !empty($filters['regions'])) {
        $rdsql = array();
        $nr = 0;
        foreach ($filters['regions'] as $rg) {
            $fparams["region{$nr}"] = $rg;
            $rdsql[] = "location_region_id=:region{$nr}";
            ++$nr;
        }
        $sql_region = ' AND ('.implode(' OR ', $rdsql).') ';
    }

    foreach ($termlist as &$t) {
        $locsql = "SELECT * FROM tbl_location WHERE location_deleted IS NULL AND location_published = 1 {$sql_franchisee}{$sql_region} AND EXISTS (SELECT classterm_id FROM tbl_classterm LEFT JOIN tbl_class ON classterm_class_id = class_object_id WHERE class_location_id = location_object_id AND class_deleted IS NULL AND class_published = 1 AND classterm_deleted IS NULL AND classterm_published = 1 AND classterm_term_id = :term_id{$lwhere}) ORDER BY location_state, location_suburb";
        $locations = $DBobject->wrappedSql($locsql, array_merge($lparams, array(':term_id' => $t['term_id']), $fparams));
        foreach ($locations as &$l) {
            $cltermwhere = ' AND classterm_term_id = :term_id AND class_location_id = :loc_id';
            $cltparams['term_id'] = $t['term_id'];
            $cltparams['loc_id'] = $l['location_object_id'];

            if (!empty($filters) && !empty($filters['weekdays'])) {
                $wdsql = array();
                $dwn = 0;
                foreach ($filters['weekdays'] as $wd) {
                    $cltparams["weekday{$dwn}"] = $wd;
                    $wdsql[] = "class_weekday like :weekday{$dwn}";
                    ++$dwn;
                }
                $cltermwhere .= ' AND ('.implode(' OR ', $wdsql).') ';
            }
            if (!empty($filters) && !empty($filters['agegroups'])) {
                $agsql = array();
                $agn = 0;
                foreach ($filters['agegroups'] as $wd) {
                    $cltparams["agegroup{$agn}"] = $wd;
                    $agsql[] = "class_agegroup_id=:agegroup{$agn}";
                    ++$agn;
                }
                $cltermwhere .= ' AND ('.implode(' OR ', $agsql).') ';
            }

            $lsql = "SELECT class_object_id,class_name,class_weekday,class_url,location_name, agegroup_name,class_size FROM tbl_class LEFT JOIN tbl_location ON class_location_id = location_object_id LEFT JOIN tbl_agegroup ON class_agegroup_id = agegroup_id  WHERE class_deleted IS NULL AND class_published = 1{$lwhere} AND agegroup_deleted IS NULL AND location_deleted IS NULL AND EXISTS (SELECT classterm_id FROM tbl_classterm WHERE classterm_class_id = class_object_id AND classterm_deleted IS NULL {$cltermwhere}) ORDER BY location_suburb, FIELD(class_weekday, 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY', 'SUNDAY'), class_start_time, agegroup_name";
            $classes = $DBobject->wrappedSql($lsql, array_merge($lparams, $cltparams));
      //get all information for students enrolled in class days after today
            foreach ($classes as &$c) {
                //       	$curesqlActive ="SELECT COUNT(DISTINCT(CONCAT(enrolment_student_id, enrolment_class_object_id))) AS enrolment FROM tbl_enrolment WHERE enrolment_deleted IS NULL AND enrolment_published = '1' AND enrolment_week >= :weekno AND enrolment_term_object_id = :term_id AND enrolment_class_object_id = :class_id AND enrolment_type = 'T'";
            //          $curenrlsActive = $DBobject->wrappedSql($curesqlActive,array("class_id"=>$c['class_object_id'],"term_id"=>$t['term_object_id'], "weekno"=>$t['w']));
                $curesqlActive = "SELECT COUNT(DISTINCT(CONCAT(enrolment_student_id, enrolment_class_object_id))) AS enrolment FROM tbl_enrolment WHERE enrolment_deleted IS NULL AND enrolment_published = '1' AND enrolment_term_object_id = :term_id AND enrolment_class_object_id = :class_id AND enrolment_type = 'T'";
                $curenrlsActive = $DBobject->wrappedSql($curesqlActive, array('class_id' => $c['class_object_id'], 'term_id' => $t['term_object_id']));
                //$curesqlActive ="SELECT COUNT(DISTINCT(CONCAT(enrolment_student_id, enrolment_class_object_id))) AS enrolment FROM tbl_enrolment WHERE enrolment_deleted IS NULL  AND enrolment_term_object_id = :term_id AND enrolment_class_object_id = :class_id AND enrolment_type = 'T' AND NOT EXISTS (SELECT tmp.enrolment_id FROM tbl_enrolment AS tmp WHERE tmp.enrolment_deleted IS NULL AND tmp.enrolment_student_id = tbl_enrolment.enrolment_student_id AND (tmp.enrolment_type = 'S' OR tmp.enrolment_type = 'MV')  AND tmp.enrolment_published = '1')";
                //$curenrlsActive = $DBobject->wrappedSql($curesqlActive,array("class_id"=>$c['class_object_id'],"term_id"=>$t['term_object_id']));

                $curesql = "SELECT COUNT(DISTINCT(CONCAT(enrolment_student_id, enrolment_class_object_id))) AS enrolment FROM tbl_enrolment WHERE enrolment_deleted IS NULL AND enrolment_term_object_id = :term_id AND enrolment_class_object_id = :class_id AND enrolment_type = 'T'";
                $curenrls = $DBobject->wrappedSql($curesql, array('class_id' => $c['class_object_id'], 'term_id' => $t['term_object_id']));
                $c['conversion']['rate'] = 0;
                $c['conversion']['enrolled'] = $curenrls[0]['enrolment'];
                $c['conversion']['active'] = $curenrlsActive[0]['enrolment'];
                $c['conversion']['reenrolled'] = 0;

                $esql = "SELECT COUNT(DISTINCT(CONCAT(enrolment_student_id, enrolment_class_object_id))) AS enrolment FROM tbl_enrolment WHERE enrolment_deleted IS NULL  AND enrolment_term_object_id = :term_id AND enrolment_class_object_id = :class_id AND enrolment_type = 'T' AND EXISTS (SELECT tmp.enrolment_id FROM tbl_enrolment AS tmp WHERE tmp.enrolment_deleted IS NULL AND tmp.enrolment_student_id = tbl_enrolment.enrolment_student_id AND (tmp.enrolment_type = 'S' OR tmp.enrolment_type = 'MV')  AND tmp.enrolment_published = '1')";
                $enrls = $DBobject->wrappedSql($esql, array('class_id' => $c['class_object_id'], 'term_id' => $t['term_object_id']));

                try {
                    $c['conversion']['rate'] = round((intval($enrls[0]['enrolment']) / intval($curenrls[0]['enrolment'])) * 100, 2);
                } catch (Exception $e) {
                }

                      $c['conversion']['enrolled'] = $curenrls[0]['enrolment'];
                      $c['conversion']['reenrolled'] = $enrls[0]['enrolment'];
            }
            if (!empty($classes)) {
                $l['classes'] = $classes;
            }
        }
        if (!empty($locations)) {
            $t['locations'] = $locations;
        }
    }

    return $termlist;
}

/**
 * Valid Filters:.
 *
 *    [weekdays] = this is the day of the week (1=Monday, 2=Tuesday, 3=Wednesday, 4=Thursday, 5=Friday, 6=Saturday, 7=Sunday
 *    [agegroups] = age group id
 *    [time] = time of day in 24hr
 *
 * @param string $id
 * @param array  $terms     An array of the term ids to list
 * @param array  $locations An array of the location ids to list
 * @param int    $week      The week number representing a particular week of the term
 * @param array  $filters
 */
function revenuecomparisonlist($id = null, $terms = array(), $locations = array(), $filters = array())
{
    global $DBobject;
    $data = array();

    $fparams = array();
    if (!empty($_SESSION['user']['admin']['franchisee'])) {
        $n = 1;
        $tmpsql = array();
        foreach ($_SESSION['user']['admin']['franchisee'] as $m) {
            $tmpsql[] = "location_franchisee_id = :franchisee{$n}";
            $fparams[":franchisee{$n}"] = $m;
            ++$n;
        }
        $sql_franchisee = ' AND ('.implode(' OR ', $tmpsql).') ';
    }

  //This sql gets the term start day as the Sunday begining the week of the selected date SELECT DAYNAME(term_start_date), DAYOFWEEK(term_start_date),SUBDATE(term_start_date, (DAYOFWEEK(term_start_date)-1)),term_start_date FROM tbl_term
    if (empty($terms)) {
        //     $tsql = "SELECT term_object_id FROM tbl_term WHERE term_deleted IS NULL AND term_published = 1 AND term_start_date <= NOW() AND term_end_date >= NOW()";
    //     $res = $DBobject->wrappedSql($tsql);
        $tsql = "SELECT term_object_id FROM tbl_term WHERE term_deleted IS NULL AND term_published = 1 AND term_start_date <= NOW() AND term_end_date >= NOW() AND term_id IN (SELECT DISTINCT(classterm_term_id) FROM tbl_classterm LEFT JOIN tbl_class ON classterm_class_id = class_object_id WHERE class_deleted IS NULL AND classterm_deleted IS NULL AND classterm_published = 1 AND class_location_id IN (SELECT location_object_id FROM tbl_location WHERE location_deleted IS NULL {$sql_franchisee})) ORDER BY term_start_date DESC";
        $res = $DBobject->wrappedSql($tsql, $fparams);
        foreach ($res as $r) {
            $terms[] = $r['term_object_id'];
        }
        if (empty($terms)) {
            return array();
        }
    }
    $cltparams = array();
  //Get week numbers for all terms or assign static week number if selected
    $n = 1;
    $termlist = array();
    foreach ($terms as $id) {
        $wsql = 'SELECT term_termcategory_id, term_region,term_name, term_start_date, term_id, term_object_id, SUBDATE(term_start_date, (DAYOFWEEK(term_start_date)-1)) AS firstsunday, '.(empty($week) ? 'IF(term_start_date < NOW(),CEIL(DATEDIFF(IF(term_end_date>NOW(),NOW(),term_start_date),SUBDATE(term_start_date, (DAYOFWEEK(term_start_date)-1)))/7),1)' : "{$week}").' AS weekno FROM tbl_term WHERE term_deleted IS NULL AND term_published = 1 AND term_object_id = :term_id';
        $tres = $DBobject->wrappedSql($wsql, array('term_id' => $id));
        $termlist[] = $tres[0];
        ++$n;
    }

  //Get Class record to show all classes for the selected term
    $lparams = array();
    $lids = array();
    $lwhere = '';
    $ln = 0;
    if (!empty($locations)) {
        foreach ($locations as $id) {
            $lids[] = "class_location_id = :local_id{$ln}";
            $lparams["local_id{$ln}"] = $id;
            ++$ln;
        }
        $lwhere = ' AND ('.implode(' OR ', $lids).')';
    }

  //Filter by region
    if (!empty($filters) && !empty($filters['regions'])) {
        $rdsql = array();
        $nr = 0;
        foreach ($filters['regions'] as $rg) {
            $fparams["region{$nr}"] = $rg;
            $rdsql[] = "location_region_id=:region{$nr}";
            ++$nr;
        }
        $sql_region = ' AND ('.implode(' OR ', $rdsql).') ';
    }

    foreach ($termlist as &$t) {
//     $tmsql = "SELECT * FROM tbl_term WHERE term_deleted IS NULL AND term_published = 1 AND term_termcategory_id < :termcat AND term_region = :region AND term_start_date < :start ORDER BY term_termcategory_id DESC LIMIT 1";
//     $preterm = $DBobject->wrappedSql($tmsql,array("termcat"=>($t["term_termcategory_id"]==1?4:$t["term_termcategory_id"]),"region"=>$t["term_region"],"start"=>$t["term_start_date"]));
        $sqlyear = "SELECT * FROM tbl_term WHERE term_termcategory_id != :term AND term_start_date <:start AND term_region = :region ORDER BY DATE_FORMAT(term_start_date,'%Y%m%d') DESC LIMIT 1";
        $preterm = $DBobject->wrappedSql($sqlyear, array('term' => $t['term_termcategory_id'], 'start' => $t['term_start_date'], 'region' => $t['term_region']));

        $locsql = "SELECT * FROM tbl_location WHERE location_deleted IS NULL AND location_published = 1 {$sql_franchisee}{$sql_region} AND EXISTS (SELECT classterm_id FROM tbl_classterm LEFT JOIN tbl_class ON classterm_class_id = class_object_id WHERE class_location_id = location_object_id AND class_deleted IS NULL AND class_published = 1 AND classterm_deleted IS NULL AND classterm_published = 1 AND (classterm_term_id = :term_id OR classterm_term_id = :pre_term_id){$lwhere}) ORDER BY location_state, location_suburb";
        $locations = $DBobject->wrappedSql($locsql, array_merge($lparams, array(':term_id' => $t['term_id'], ':pre_term_id' => $preterm[0]['term_id']), $fparams));
        foreach ($locations as &$l) {
            $cltermwhere = ' AND (classterm_term_id = :term_id OR classterm_term_id = :pre_term_id)AND class_location_id = :loc_id';
            $cltparams['term_id'] = $t['term_id'];
            $cltparams['pre_term_id'] = $preterm[0]['term_id'];
            $cltparams['loc_id'] = $l['location_object_id'];

            if (!empty($filters) && !empty($filters['weekdays'])) {
                $wdsql = array();
                $dwn = 0;
                foreach ($filters['weekdays'] as $wd) {
                    $cltparams["weekday{$dwn}"] = $wd;
                    $wdsql[] = "class_weekday like :weekday{$dwn}";
                    ++$dwn;
                }
                $cltermwhere .= ' AND ('.implode(' OR ', $wdsql).') ';
            }
            if (!empty($filters) && !empty($filters['agegroups'])) {
                $agsql = array();
                $agn = 0;
                foreach ($filters['agegroups'] as $wd) {
                    $cltparams["agegroup{$agn}"] = $wd;
                    $agsql[] = "class_agegroup_id=:agegroup{$agn}";
                    ++$agn;
                }
                $cltermwhere .= ' AND ('.implode(' OR ', $agsql).') ';
            }

            $lsql = "SELECT class_object_id,class_name,class_weekday,class_url,location_name, agegroup_name,class_size FROM tbl_class LEFT JOIN tbl_location ON class_location_id = location_object_id LEFT JOIN tbl_agegroup ON class_agegroup_id = agegroup_id  WHERE class_deleted IS NULL AND class_published = 1{$lwhere} AND agegroup_deleted IS NULL AND location_deleted IS NULL AND EXISTS (SELECT classterm_id FROM tbl_classterm WHERE classterm_class_id = class_object_id AND classterm_deleted IS NULL {$cltermwhere}) ORDER BY location_suburb, FIELD(class_weekday, 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY', 'SUNDAY'), class_start_time, agegroup_name";
            $classes = $DBobject->wrappedSql($lsql, array_merge($lparams, $cltparams));

      //get all information for students enrolled in class days after today
            $sum = 0;
            $presum = 0;
            foreach ($classes as &$c) {
                $sql_filters = " AND payment_id IN (SELECT order_payment_id FROM tbl_order LEFT JOIN tbl_status ON order_status_id = status_id WHERE order_deleted IS NULL AND status_type != 'partial' )";
                $psql_filters = " AND payment_id IN (SELECT order_payment_id FROM tbl_order LEFT JOIN tbl_status ON order_status_id = status_id WHERE order_deleted IS NULL AND status_type = 'partial' )";
        //$rsql = "SELECT SUM(payment_total) AS revenue FROM tbl_payment WHERE payment_status = 'A' AND payment_deleted IS NULL AND EXISTS (SELECT * FROM tbl_enrolment WHERE (enrolment_type != 'MK' AND enrolment_type != 'T') AND enrolment_payment_id = payment_id AND enrolment_published = 1 AND enrolment_deleted IS NULL AND enrolment_class_object_id = :class_id AND enrolment_term_object_id = :term_id) {$sql_filters}";
                $subrsql = "SELECT payment_total AS total, payment_id FROM tbl_payment LEFT JOIN tbl_enrolment ON payment_id = enrolment_payment_id WHERE payment_status = 'A' AND payment_deleted IS NULL AND enrolment_type != 'MK' AND enrolment_type != 'T' AND enrolment_payment_id = payment_id AND enrolment_published = 1 AND enrolment_deleted IS NULL AND enrolment_class_object_id = :class_id AND enrolment_term_object_id = :term_id {$sql_filters}  GROUP BY payment_id";
                $rsql = "SELECT SUM(total) AS revenue FROM ({$subrsql}) AS TMP";
                $ressum = $DBobject->wrappedSql($rsql, array(':class_id' => $c['class_object_id'], ':term_id' => $t['term_object_id']));

        //$rsql2 = "SELECT SUM(payment_charged_amount) AS revenue FROM tbl_payment WHERE payment_status = 'A' AND payment_deleted IS NULL AND EXISTS (SELECT * FROM tbl_enrolment WHERE (enrolment_type != 'MK' AND enrolment_type != 'T') AND enrolment_payment_id = payment_id AND enrolment_published = 1 AND enrolment_deleted IS NULL AND enrolment_class_object_id = :class_id AND enrolment_term_object_id = :term_id) {$psql_filters}";
                $subrsql2 = "SELECT payment_charged_amount AS total, payment_id FROM tbl_payment LEFT JOIN tbl_enrolment ON payment_id = enrolment_payment_id WHERE payment_status = 'A' AND payment_deleted IS NULL AND enrolment_type != 'MK' AND enrolment_type != 'T' AND enrolment_published = 1 AND enrolment_deleted IS NULL AND enrolment_class_object_id = :class_id AND enrolment_term_object_id = :term_id {$psql_filters} GROUP BY payment_id";
                $rsql2 = "SELECT SUM(total) AS revenue FROM ({$subrsql2}) AS TMP";
                $ressum2 = $DBobject->wrappedSql($rsql2, array(':class_id' => $c['class_object_id'], ':term_id' => $t['term_object_id']));
                setlocale(LC_MONETARY, 'en_AU');
                $c['revenue'] = money_format('%i', $ressum[0]['revenue'] + $ressum2[0]['revenue']);
                $sum += $ressum[0]['revenue'];

        //$rsql = "SELECT SUM(payment_charged_amount) AS revenue FROM tbl_payment WHERE payment_status = 'A' AND payment_deleted IS NULL AND EXISTS (SELECT * FROM tbl_enrolment WHERE (enrolment_type != 'MK' AND enrolment_type != 'T') AND enrolment_payment_id = payment_id AND enrolment_published = 1 AND enrolment_deleted IS NULL AND enrolment_class_object_id = :class_id AND enrolment_term_object_id = :term_id) {$sql_filters}";
                $ressum = $DBobject->wrappedSql($rsql, array(':class_id' => $c['class_object_id'], ':term_id' => $preterm[0]['term_object_id']));

        //$rsql2 = "SELECT SUM(payment_charged_amount) AS revenue FROM tbl_payment WHERE payment_status = 'A' AND payment_deleted IS NULL AND EXISTS (SELECT * FROM tbl_enrolment WHERE (enrolment_type != 'MK' AND enrolment_type != 'T') AND enrolment_payment_id = payment_id AND enrolment_published = 1 AND enrolment_deleted IS NULL AND enrolment_class_object_id = :class_id AND enrolment_term_object_id = :term_id) {$psql_filters}";
                $ressum2 = $DBobject->wrappedSql($rsql2, array(':class_id' => $c['class_object_id'], ':term_id' => $preterm[0]['term_object_id']));
                setlocale(LC_MONETARY, 'en_AU');
                $c['prerevenue'] = money_format('%i', $ressum[0]['revenue'] + $ressum2[0]['revenue']);
                $presum += $ressum[0]['revenue'];
            }

            $l['total'] = money_format('%i', $sum);
            $l['pretotal'] = money_format('%i', $presum);
            if (!empty($classes)) {
                $l['classes'] = $classes;
            }
        }
        if (!empty($locations)) {
            $t['locations'] = $locations;
        }
    }

    return $termlist;
}

/**
 * @return multitype:number
 */
function averagerevenuelist($id = null, $terms = array(), $locations = array(), $filters = array(), $enrolmenttype = array('S', 'MV'))
{
    global $DBobject;
    $data = array();

    $params = array();
    $lwhere = '';
    if (!empty($_SESSION['user']['admin']['franchisee'])) {
        $n = 1;
        $tmpsql = array();
        foreach ($_SESSION['user']['admin']['franchisee'] as $m) {
            $tmpsql[] = "location_franchisee_id = :franchisee{$n}";
            $params[":franchisee{$n}"] = $m;
            ++$n;
        }
        $lwhere .= ' AND ('.implode(' OR ', $tmpsql).') ';
    }

    $tids = array();
    if (!empty($terms)) {
        $n = 1;
        foreach ($terms as $id) {
            $tids[] = "enrolment_term_object_id = :term_id{$n}";
            $params["term_id{$n}"] = $id;
            ++$n;
        }
        $lwhere .= ' AND ('.implode(' OR ', $tids).')';
    }
    $lids = array();
    if (!empty($locations)) {
        $n = 1;
        foreach ($locations as $id) {
            $lids[] = "class_location_id = :local_id{$n}";
            $params["local_id{$n}"] = $id;
            ++$n;
        }
        $lwhere .= ' AND ('.implode(' OR ', $lids).')';
    }
    if (!empty($filters) && !empty($filters['regions'])) {
        $rdsql = array();
        $nr = 0;
        foreach ($filters['regions'] as $rg) {
            $rdsql[] = "location_region_id=:region{$nr}";
            $params["region{$nr}"] = $rg;
            ++$nr;
        }
        $lwhere .= ' AND ('.implode(' OR ', $rdsql).') ';
    }
    if (!empty($filters) && !empty($filters['weekdays'])) {
        $wdsql = array();
        $dwn = 0;
        foreach ($filters['weekdays'] as $wd) {
            $params["weekday{$dwn}"] = $wd;
            $wdsql[] = "class_weekday like :weekday{$dwn}";
            ++$dwn;
        }
        $lwhere .= ' AND ('.implode(' OR ', $wdsql).') ';
    }
    if (!empty($filters) && !empty($filters['agegroups'])) {
        $agsql = array();
        $agn = 0;
        foreach ($filters['agegroups'] as $wd) {
            $params["agegroup{$agn}"] = $wd;
            $agsql[] = "class_agegroup_id=:agegroup{$agn}";
            ++$agn;
        }
        $lwhere .= ' AND ('.implode(' OR ', $agsql).') ';
    }
    $stn = 0;
    $stsql = array();
    if (!empty($enrolmenttype)) {
        foreach ($enrolmenttype as $s) {
            $params["enrltype{$stn}"] = $s;
            $stsql[] = "enrolment_type=:enrltype{$stn}";
            ++$stn;
        }
        $lwhere .= ' AND ('.implode(' OR ', $stsql).') ';
    }

  //$sql_enrolments = " AND payment_id IN (SELECT DISTINCT(enrolment_payment_id) FROM tbl_enrolment LEFT JOIN tbl_class ON enrolment_class_object_id = class_object_id LEFT JOIN tbl_location ON class_location_id = location_object_id WHERE location_deleted IS NULL AND class_deleted IS NULL AND enrolment_deleted IS NULL AND enrolment_published = '1' {$lwhere} ) ";

    $sql_filters = " AND payment_id IN (SELECT order_payment_id FROM tbl_order LEFT JOIN tbl_status ON order_status_id = status_id WHERE order_deleted IS NULL AND status_type != 'partial' )";
    $psql_filters = " AND payment_id IN (SELECT order_payment_id FROM tbl_order LEFT JOIN tbl_status ON order_status_id = status_id WHERE order_deleted IS NULL AND status_type = 'partial' )";
  //$tsql = "SELECT COUNT(DISTINCT(CONCAT(enrolment_student_id,enrolment_term_object_id, enrolment_class_object_id))) AS cnt,payment_id,payment_total AS payment_charged_amount FROM tbl_payment LEFT JOIN tbl_enrolment ON payment_id = enrolment_payment_id WHERE payment_deleted IS NULL AND payment_status = 'A' AND (enrolment_type = 'S' OR enrolment_type = 'MV') AND enrolment_deleted IS NULL AND enrolment_published = 1 {$sql_enrolments} {$sql_filters} GROUP BY payment_id";
    $tsql = "SELECT COUNT(DISTINCT(CONCAT(enrolment_student_id,enrolment_term_object_id, enrolment_class_object_id))) AS cnt,payment_id,payment_total AS payment_charged_amount FROM tbl_payment LEFT JOIN tbl_enrolment ON payment_id = enrolment_payment_id LEFT JOIN tbl_class ON enrolment_class_object_id = class_object_id LEFT JOIN tbl_location ON class_location_id = location_object_id WHERE payment_deleted IS NULL AND payment_status = 'A' AND (enrolment_type = 'S' OR enrolment_type = 'MV') AND location_deleted IS NULL AND class_deleted IS NULL AND enrolment_deleted IS NULL AND enrolment_published = 1 {$sql_filters} {$lwhere} GROUP BY payment_id";

    $res = $DBobject->wrappedSql($tsql, $params);
    $students = 0;
    $revenue = 0.0;
    foreach ($res as $r) {
        $students += $r['cnt'];
        $revenue += $r['payment_charged_amount'];
    }
  //$tsql2 = "SELECT COUNT(DISTINCT(CONCAT(enrolment_student_id,enrolment_term_object_id, enrolment_class_object_id))) AS cnt,payment_id,payment_charged_amount FROM tbl_payment LEFT JOIN tbl_enrolment ON payment_id = enrolment_payment_id WHERE payment_deleted IS NULL AND payment_status = 'A' AND (enrolment_type = 'S' OR enrolment_type = 'MV') AND enrolment_deleted IS NULL AND enrolment_published = 1 {$sql_enrolments} {$psql_filters} GROUP BY payment_id";
    $tsql2 = "SELECT COUNT(DISTINCT(CONCAT(enrolment_student_id,enrolment_term_object_id, enrolment_class_object_id))) AS cnt,payment_id,payment_charged_amount FROM tbl_payment LEFT JOIN tbl_enrolment ON payment_id = enrolment_payment_id LEFT JOIN tbl_class ON enrolment_class_object_id = class_object_id LEFT JOIN tbl_location ON class_location_id = location_object_id WHERE payment_deleted IS NULL AND payment_status = 'A' AND (enrolment_type = 'S' OR enrolment_type = 'MV') AND location_deleted IS NULL AND class_deleted IS NULL AND enrolment_deleted IS NULL AND enrolment_published = 1 {$sql_enrolments} {$psql_filters} GROUP BY payment_id";
    $res2 = $DBobject->wrappedSql($tsql2, $params);
    foreach ($res2 as $r) {
        $students += $r['cnt'];
        $revenue += $r['payment_charged_amount'];
    }

    setlocale(LC_MONETARY, 'en_AU');
    $t['totalenrolments'] = $students;
    $t['totalrevenue'] = money_format('%i', $revenue);
    $t['avgrevenue'] = money_format('%i', $revenue / $students);
    $data[] = $t;

    return $data;
}

/**
 * @return multitype:number
 */
function averageenrolmentslist($id = null, $terms = array(), $locations = array(), $filters = array(), $enrolmenttype = array())
{
    global $DBobject;
    $data = array();

    $params = array();
    $lwhere = '';
    if (!empty($_SESSION['user']['admin']['franchisee'])) {
        $n = 1;
        $tmpsql = array();
        foreach ($_SESSION['user']['admin']['franchisee'] as $m) {
            $tmpsql[] = "location_franchisee_id = :franchisee{$n}";
            $params[":franchisee{$n}"] = $m;
            ++$n;
        }
        $lwhere .= ' AND ('.implode(' OR ', $tmpsql).') ';
    }

    $tids = array();
    if (!empty($terms)) {
        $n = 1;
        foreach ($terms as $id) {
            $tids[] = "enrolment_term_object_id = :term_id{$n}";
            $params["term_id{$n}"] = $id;
            ++$n;
        }
        $lwhere .= ' AND ('.implode(' OR ', $tids).')';
    }
    $lids = array();
    if (!empty($locations)) {
        $n = 1;
        foreach ($locations as $id) {
            $lids[] = "class_location_id = :local_id{$n}";
            $params["local_id{$n}"] = $id;
            ++$n;
        }
        $lwhere .= ' AND ('.implode(' OR ', $lids).')';
    }
    if (!empty($filters) && !empty($filters['regions'])) {
        $rdsql = array();
        $nr = 0;
        foreach ($filters['regions'] as $rg) {
            $rdsql[] = "location_region_id=:region{$nr}";
            $params["region{$nr}"] = $rg;
            ++$nr;
        }
        $lwhere .= ' AND ('.implode(' OR ', $rdsql).') ';
    }
    if (!empty($filters) && !empty($filters['weekdays'])) {
        $wdsql = array();
        $dwn = 0;
        foreach ($filters['weekdays'] as $wd) {
            $params["weekday{$dwn}"] = $wd;
            $wdsql[] = "class_weekday like :weekday{$dwn}";
            ++$dwn;
        }
        $lwhere .= ' AND ('.implode(' OR ', $wdsql).') ';
    }
    if (!empty($filters) && !empty($filters['agegroups'])) {
        $agsql = array();
        $agn = 0;
        foreach ($filters['agegroups'] as $wd) {
            $params["agegroup{$agn}"] = $wd;
            $agsql[] = "class_agegroup_id=:agegroup{$agn}";
            ++$agn;
        }
        $lwhere .= ' AND ('.implode(' OR ', $agsql).') ';
    }
    $stn = 0;
    $stsql = array();
    if (!empty($enrolmenttype)) {
        foreach ($enrolmenttype as $s) {
            $params["enrltype{$stn}"] = $s;
            $stsql[] = "enrolment_type=:enrltype{$stn}";
            ++$stn;
        }
        $lwhere .= ' AND ('.implode(' OR ', $stsql).') ';
    }
    $sql_enrolments = " AND enrolment_id IN (SELECT DISTINCT(enrolment_id) FROM tbl_enrolment LEFT JOIN tbl_class ON enrolment_class_object_id = class_object_id LEFT JOIN tbl_location ON class_location_id = location_object_id WHERE location_deleted IS NULL AND class_deleted IS NULL AND enrolment_deleted IS NULL AND enrolment_published = '1' {$lwhere} ) ";

  //$tsql = "SELECT COUNT(DISTINCT(CONCAT(enrolment_student_id,enrolment_term_object_id, enrolment_class_object_id))) AS cnt, CONCAT(enrolment_term_object_id, enrolment_class_object_id) FROM tbl_enrolment WHERE (enrolment_type = 'S' OR enrolment_type = 'MV') AND enrolment_deleted IS NULL AND enrolment_published = 1 {$sql_franchisee} GROUP BY CONCAT(enrolment_term_object_id, enrolment_class_object_id)";
    $tsql = "SELECT COUNT(DISTINCT(CONCAT(enrolment_student_id,enrolment_term_object_id, enrolment_class_object_id))) AS cnt, CONCAT(enrolment_term_object_id, enrolment_class_object_id) FROM tbl_enrolment WHERE enrolment_deleted IS NULL AND enrolment_published = 1 {$sql_enrolments} GROUP BY CONCAT(enrolment_term_object_id, enrolment_class_object_id)";
    $res = $DBobject->wrappedSql($tsql, $params);
    $students = 0.0;
    $class = 0.0;
    foreach ($res as $r) {
        $students += $r['cnt'];
        ++$class;
    }
    setlocale(LC_MONETARY, 'en_AU');
    $t['totalenrolments'] = $students;
    $t['totalclasses'] = $class;
    $t['avgenrolments'] = number_format($students / $class, 2);
    $data[] = $t;

    return $data;
}

/**
 * @return multitype:number
 */
function referrallist($id = null, $terms = array(), $locations = array(), $filters = array(), $enrolmenttype = array('S', 'MV'))
{
    global $DBobject;

    $params = array();
    $lwhere = '';
    if (!empty($_SESSION['user']['admin']['franchisee'])) {
        $n = 1;
        $tmpsql = array();
        foreach ($_SESSION['user']['admin']['franchisee'] as $m) {
            $tmpsql[] = "location_franchisee_id = :franchisee{$n}";
            $params[":franchisee{$n}"] = $m;
            ++$n;
        }
        $lwhere .= ' AND ('.implode(' OR ', $tmpsql).') ';
    }

    $tids = array();
    if (!empty($terms)) {
        $n = 1;
        foreach ($terms as $id) {
            $tids[] = "enrolment_term_object_id = :term_id{$n}";
            $params["term_id{$n}"] = $id;
            ++$n;
        }
        $lwhere .= ' AND ('.implode(' OR ', $tids).')';
    }
    $lids = array();
    if (!empty($locations)) {
        $n = 1;
        foreach ($locations as $id) {
            $lids[] = "class_location_id = :local_id{$n}";
            $params["local_id{$n}"] = $id;
            ++$n;
        }
        $lwhere .= ' AND ('.implode(' OR ', $lids).')';
    }
    if (!empty($filters) && !empty($filters['regions'])) {
        $rdsql = array();
        $nr = 0;
        foreach ($filters['regions'] as $rg) {
            $rdsql[] = "location_region_id=:region{$nr}";
            $params["region{$nr}"] = $rg;
            ++$nr;
        }
        $lwhere .= ' AND ('.implode(' OR ', $rdsql).') ';
    }
    if (!empty($filters) && !empty($filters['weekdays'])) {
        $wdsql = array();
        $dwn = 0;
        foreach ($filters['weekdays'] as $wd) {
            $params["weekday{$dwn}"] = $wd;
            $wdsql[] = "class_weekday like :weekday{$dwn}";
            ++$dwn;
        }
        $lwhere .= ' AND ('.implode(' OR ', $wdsql).') ';
    }
    if (!empty($filters) && !empty($filters['agegroups'])) {
        $agsql = array();
        $agn = 0;
        foreach ($filters['agegroups'] as $wd) {
            $params["agegroup{$agn}"] = $wd;
            $agsql[] = "class_agegroup_id=:agegroup{$agn}";
            ++$agn;
        }
        $lwhere .= ' AND ('.implode(' OR ', $agsql).') ';
    }
    $stn = 0;
    $stsql = array();
    if (!empty($enrolmenttype)) {
        foreach ($enrolmenttype as $s) {
            $params["enrltype{$stn}"] = $s;
            $stsql[] = "enrolment_type=:enrltype{$stn}";
            ++$stn;
        }
        $lwhere .= ' AND ('.implode(' OR ', $stsql).') ';
    }
    $sql_enrolments = " AND user_id IN (SELECT DISTINCT(student_user_id) FROM tbl_student LEFT JOIN tbl_enrolment ON student_id = enrolment_student_id LEFT JOIN tbl_class ON enrolment_class_object_id = class_object_id LEFT JOIN tbl_location ON class_location_id = location_object_id WHERE location_deleted IS NULL AND class_deleted IS NULL AND enrolment_deleted IS NULL AND enrolment_published = '1' {$lwhere} ) ";

    $tsql = "SELECT COUNT(user_id) AS cnt,user_hearabout FROM tbl_user WHERE user_hearabout IS NOT NULL AND user_deleted IS NULL {$sql_enrolments} GROUP BY user_hearabout ORDER BY COUNT(user_id) DESC";
    $res = $DBobject->wrappedSql($tsql, $params);

    return $res;
}

/**
 * Valid Filters:.
 *
 *    [weekdays] = this is the day of the week (1=Monday, 2=Tuesday, 3=Wednesday, 4=Thursday, 5=Friday, 6=Saturday, 7=Sunday
 *    [agegroups] = age group id
 *    [time] = time of day in 24hr
 *
 * @param string $id
 * @param array  $terms     An array of the term ids to list
 * @param array  $locations An array of the location ids to list
 * @param int    $week      The week number representing a particular week of the term
 * @param array  $filters
 */
function toplocationslist($id = null, $terms = array(), $locations = array(), $filters = array(), $enrolmenttype = array())
{
    global $DBobject;

    $fparams = array();
    if (!empty($_SESSION['user']['admin']['franchisee'])) {
        $n = 1;
        $tmpsql = array();
        foreach ($_SESSION['user']['admin']['franchisee'] as $m) {
            $tmpsql[] = "location_franchisee_id = :franchisee{$n}";
            $fparams[":franchisee{$n}"] = $m;
            ++$n;
        }
        $sql_franchisee = ' AND ('.implode(' OR ', $tmpsql).') ';
    }

    $stn = 0;
    $stsql = array();
    $lwhere = '';
    if (!empty($enrolmenttype)) {
        foreach ($enrolmenttype as $s) {
            $fparams["enrltype{$stn}"] = $s;
            $stsql[] = "enrolment_type=:enrltype{$stn}";
            ++$stn;
        }
        $lwhere .= ' AND ('.implode(' OR ', $stsql).') ';
    }

  //Filter by region

    if (!empty($filters) && !empty($filters['regions'])) {
        $rdsql = array();
        $nr = 0;
        foreach ($filters['regions'] as $rg) {
            $fparams["region{$nr}"] = $rg;
            $rdsql[] = "location_region_id=:region{$nr}";
            ++$nr;
        }
        $lwhere .= ' AND ('.implode(' OR ', $rdsql).') ';
    }

  //This sql gets the term start day as the Sunday begining the week of the selected date SELECT DAYNAME(term_start_date), DAYOFWEEK(term_start_date),SUBDATE(term_start_date, (DAYOFWEEK(term_start_date)-1)),term_start_date FROM tbl_term
    $termlist = array();
    if (empty($terms)) {
        $locsql = "SELECT *, COUNT(DISTINCT(CONCAT(enrolment_student_id,enrolment_term_object_id))) AS cnt FROM tbl_location LEFT JOIN tbl_class ON location_object_id = class_location_id LEFT JOIN tbl_enrolment ON class_object_id = enrolment_class_object_id WHERE location_deleted IS NULL AND location_published = 1 {$sql_franchisee} {$lwhere} AND enrolment_deleted IS NULL AND enrolment_published = 1 AND EXISTS (SELECT classterm_id FROM tbl_classterm LEFT JOIN tbl_class ON classterm_class_id = class_object_id WHERE class_location_id = location_object_id AND class_deleted IS NULL AND class_published = 1 AND classterm_deleted IS NULL AND classterm_published = 1) AND EXISTS (SELECT * FROM tbl_term WHERE term_deleted IS NULL AND term_published = 1 AND term_object_id = enrolment_term_object_id) GROUP BY CONCAT(location_object_id) ORDER BY COUNT(DISTINCT(CONCAT(enrolment_student_id,enrolment_term_object_id))) DESC";
        $locations = $DBobject->wrappedSql($locsql, $fparams);
        $termlist[] = array('term_name' => 'All Terms', 'locations' => $locations);
    } else {
        //Get week numbers for all terms or assign static week number if selected
        $n = 1;
        foreach ($terms as $id) {
            $wsql = 'SELECT term_name,term_region, term_id, term_object_id, SUBDATE(term_start_date, (DAYOFWEEK(term_start_date)-1)) AS firstsunday, '.(empty($week) ? 'IF(term_start_date < NOW(),CEIL(DATEDIFF(IF(term_end_date>NOW(),NOW(),term_start_date),SUBDATE(term_start_date, (DAYOFWEEK(term_start_date)-1)))/7),1)' : "{$week}").' AS weekno FROM tbl_term WHERE term_deleted IS NULL AND term_published = 1 AND term_object_id = :term_id';
            $tres = $DBobject->wrappedSql($wsql, array('term_id' => $id));
            $termlist[] = $tres[0];
            ++$n;
        }

        foreach ($termlist as &$t) {
            $locsql = "SELECT *, COUNT(DISTINCT(CONCAT(enrolment_student_id,enrolment_term_object_id))) AS cnt FROM tbl_location LEFT JOIN tbl_class ON location_object_id = class_location_id LEFT JOIN tbl_enrolment ON class_object_id = enrolment_class_object_id WHERE enrolment_term_object_id = :term_obj_id AND location_deleted IS NULL AND location_published = 1 {$sql_franchisee} {$lwhere} AND enrolment_deleted IS NULL AND enrolment_published = 1 AND EXISTS (SELECT classterm_id FROM tbl_classterm LEFT JOIN tbl_class ON classterm_class_id = class_object_id WHERE class_location_id = location_object_id AND class_deleted IS NULL AND class_published = 1 AND classterm_deleted IS NULL AND classterm_published = 1 AND classterm_term_id = :term_id) AND EXISTS (SELECT * FROM tbl_term WHERE term_deleted IS NULL AND term_published = 1 AND term_object_id = enrolment_term_object_id) GROUP BY CONCAT(location_object_id) ORDER BY COUNT(DISTINCT(CONCAT(enrolment_student_id,enrolment_term_object_id))) DESC";
            $locations = $DBobject->wrappedSql($locsql, array_merge(array(':term_id' => $t['term_id'], ':term_obj_id' => $t['term_object_id']), $fparams));
            if (!empty($locations)) {
                $t['locations'] = $locations;
            }
        }
    }

    return $termlist;
}

/**
 *
 */
function warninglist($id = null, $terms = array(), $locations = array(), $filters = array(), $enrolmenttype = array())
{
    global $DBobject;

    $params = array();
    $lwhere = '';
    if (!empty($_SESSION['user']['admin']['franchisee'])) {
        $n = 1;
        $tmpsql = array();
        foreach ($_SESSION['user']['admin']['franchisee'] as $m) {
            $tmpsql[] = "location_franchisee_id = :franchisee{$n}";
            $params[":franchisee{$n}"] = $m;
            ++$n;
        }
        $lwhere .= ' AND ('.implode(' OR ', $tmpsql).') ';
    }

    $tids = array();
    if (!empty($terms)) {
        $n = 1;
        foreach ($terms as $id) {
            $tids[] = "enrolment_term_object_id = :term_id{$n}";
            $params["term_id{$n}"] = $id;
            ++$n;
        }
        $lwhere .= ' AND ('.implode(' OR ', $tids).')';
    }
    $lids = array();
    if (!empty($locations)) {
        $n = 1;
        foreach ($locations as $id) {
            $lids[] = "class_location_id = :local_id{$n}";
            $params["local_id{$n}"] = $id;
            ++$n;
        }
        $lwhere .= ' AND ('.implode(' OR ', $lids).')';
    }
    if (!empty($filters) && !empty($filters['regions'])) {
        $rdsql = array();
        $nr = 0;
        foreach ($filters['regions'] as $rg) {
            $rdsql[] = "location_region_id=:region{$nr}";
            $params["region{$nr}"] = $rg;
            ++$nr;
        }
        $lwhere .= ' AND ('.implode(' OR ', $rdsql).') ';
    }
    if (!empty($filters) && !empty($filters['weekdays'])) {
        $wdsql = array();
        $dwn = 0;
        foreach ($filters['weekdays'] as $wd) {
            $params["weekday{$dwn}"] = $wd;
            $wdsql[] = "class_weekday like :weekday{$dwn}";
            ++$dwn;
        }
        $lwhere .= ' AND ('.implode(' OR ', $wdsql).') ';
    }
    if (!empty($filters) && !empty($filters['agegroups'])) {
        $agsql = array();
        $agn = 0;
        foreach ($filters['agegroups'] as $wd) {
            $params["agegroup{$agn}"] = $wd;
            $agsql[] = "class_agegroup_id=:agegroup{$agn}";
            ++$agn;
        }
        $lwhere .= ' AND ('.implode(' OR ', $agsql).') ';
    }
    $stn = 0;
    $stsql = array();
    if (!empty($enrolmenttype)) {
        foreach ($enrolmenttype as $s) {
            $params["enrltype{$stn}"] = $s;
            $stsql[] = "enrolment_type=:enrltype{$stn}";
            ++$stn;
        }
        $lwhere .= ' AND ('.implode(' OR ', $stsql).') ';
    }
    $sql_enrolments = " AND user_id IN (SELECT DISTINCT(student_user_id) FROM tbl_student LEFT JOIN tbl_enrolment ON student_id = enrolment_student_id LEFT JOIN tbl_class ON enrolment_class_object_id = class_object_id LEFT JOIN tbl_location ON class_location_id = location_object_id WHERE location_deleted IS NULL AND class_deleted IS NULL AND enrolment_deleted IS NULL AND enrolment_published = '1' {$lwhere} ) ";

  //This sql gets the term start day as the Sunday begining the week of the selected date SELECT DAYNAME(term_start_date), DAYOFWEEK(term_start_date),SUBDATE(term_start_date, (DAYOFWEEK(term_start_date)-1)),term_start_date FROM tbl_term
    $locsql = "SELECT * FROM tbl_user WHERE user_warning IS NOT NULL AND user_warning != '' AND user_deleted IS NULL {$sql_enrolments} ORDER BY user_gname ASC";
    $res = $DBobject->wrappedSql($locsql, $params);

    return $res;
}

/**
 * Valid Filters:.
 *
 *    [weekdays] = this is the day of the week (1=Monday, 2=Tuesday, 3=Wednesday, 4=Thursday, 5=Friday, 6=Saturday, 7=Sunday
 *    [agegroups] = age group id
 *    [time] = time of day in 24hr
 *
 * @param string $id
 * @param array  $terms     An array of the term ids to list
 * @param array  $locations An array of the location ids to list
 * @param int    $week      The week number representing a particular week of the term
 * @param array  $filters
 */
function limitedenrolmentslist($id = null, $terms = array(), $locations = array(), $filters = array(), $enrolmenttype = array())
{
    global $DBobject;

    $fparams = array();
    if (!empty($_SESSION['user']['admin']['franchisee'])) {
        $n = 1;
        $tmpsql = array();
        foreach ($_SESSION['user']['admin']['franchisee'] as $m) {
            $tmpsql[] = "location_franchisee_id = :franchisee{$n}";
            $fparams[":franchisee{$n}"] = $m;
            ++$n;
        }
        $sql_franchisee = ' AND ('.implode(' OR ', $tmpsql).') ';
    }

  //This sql gets the term start day as the Sunday begining the week of the selected date SELECT DAYNAME(term_start_date), DAYOFWEEK(term_start_date),SUBDATE(term_start_date, (DAYOFWEEK(term_start_date)-1)),term_start_date FROM tbl_term
    if (empty($terms)) {
        //     $tsql = "SELECT term_object_id FROM tbl_term WHERE term_deleted IS NULL AND term_published = 1 AND term_start_date <= NOW() AND term_end_date >= NOW()";
    //     $res = $DBobject->wrappedSql($tsql);
        $tsql = "SELECT term_object_id FROM tbl_term WHERE term_deleted IS NULL AND term_published = 1 AND term_start_date <= NOW() AND term_end_date >= NOW() AND term_id IN (SELECT DISTINCT(classterm_term_id) FROM tbl_classterm LEFT JOIN tbl_class ON classterm_class_id = class_object_id WHERE class_deleted IS NULL AND classterm_deleted IS NULL AND classterm_published = 1 AND class_location_id IN (SELECT location_object_id FROM tbl_location WHERE location_deleted IS NULL {$sql_franchisee})) ORDER BY term_start_date DESC";
        $res = $DBobject->wrappedSql($tsql, $fparams);
        foreach ($res as $r) {
            $terms[] = $r['term_object_id'];
        }
        if (empty($terms)) {
            return array();
        }
    }
    $cltparams = array();
  //Get week numbers for all terms or assign static week number if selected
    $n = 1;
    $termlist = array();
    foreach ($terms as $id) {
        $wsql = 'SELECT term_name,term_region, term_id, term_object_id, SUBDATE(term_start_date, (DAYOFWEEK(term_start_date)-1)) AS firstsunday, '.(empty($week) ? 'IF(term_start_date < NOW(),CEIL(DATEDIFF(IF(term_end_date>NOW(),NOW(),term_start_date),SUBDATE(term_start_date, (DAYOFWEEK(term_start_date)-1)))/7),1)' : "{$week}").' AS weekno FROM tbl_term WHERE term_deleted IS NULL AND term_published = 1 AND term_object_id = :term_id';
        $tres = $DBobject->wrappedSql($wsql, array('term_id' => $id));
        $termlist[] = $tres[0];
        ++$n;
    }

  //Get Class record to show all classes for the selected term
    $lparams = array();
    $lids = array();
    $lwhere = '';
    $ln = 0;
    if (!empty($locations)) {
        foreach ($locations as $id) {
            $lids[] = "class_location_id = :local_id{$ln}";
            $lparams["local_id{$ln}"] = $id;
            ++$ln;
        }
        $lwhere = ' AND ('.implode(' OR ', $lids).')';
    }

  //Filter by region
    if (!empty($filters) && !empty($filters['regions'])) {
        $rdsql = array();
        $nr = 0;
        foreach ($filters['regions'] as $rg) {
            $fparams["region{$nr}"] = $rg;
            $rdsql[] = "location_region_id=:region{$nr}";
            ++$nr;
        }
        $sql_region = ' AND ('.implode(' OR ', $rdsql).') ';
    }

    foreach ($termlist as &$t) {
        $locsql = "SELECT tbl_location.* FROM tbl_location LEFT JOIN tbl_class ON class_location_id = location_object_id LEFT JOIN tbl_classterm ON classterm_class_id = class_object_id WHERE location_deleted IS NULL AND location_published = 1 AND class_deleted IS NULL AND class_published = 1 AND classterm_deleted IS NULL AND classterm_published = 1 {$sql_franchisee}{$sql_region} AND classterm_term_id = :term_id{$lwhere} GROUP BY location_object_id ORDER BY location_state, location_suburb";
        $locations = $DBobject->wrappedSql($locsql, array_merge($lparams, array(':term_id' => $t['term_id']), $fparams));
        foreach ($locations as &$l) {
            $cltermwhere = ' AND classterm_term_id = :term_id AND class_location_id = :loc_id';
            $cltparams['term_id'] = $t['term_id'];
            $cltparams['term_object_id'] = $t['term_object_id'];
            $cltparams['loc_id'] = $l['location_object_id'];

            if (!empty($filters) && !empty($filters['weekdays'])) {
                $wdsql = array();
                $dwn = 0;
                foreach ($filters['weekdays'] as $wd) {
                    $cltparams["weekday{$dwn}"] = $wd;
                    $wdsql[] = "class_weekday like :weekday{$dwn}";
                    ++$dwn;
                }
                $cltermwhere .= ' AND ('.implode(' OR ', $wdsql).') ';
            }
            if (!empty($filters) && !empty($filters['agegroups'])) {
                $agsql = array();
                $agn = 0;
                foreach ($filters['agegroups'] as $wd) {
                    $cltparams["agegroup{$agn}"] = $wd;
                    $agsql[] = "class_agegroup_id=:agegroup{$agn}";
                    ++$agn;
                }
                $cltermwhere .= ' AND ('.implode(' OR ', $agsql).') ';
            }

            $stn = 0;
            $stparams = array();
            $stsql = array();
            foreach ($enrolmenttype as $s) {
                $stparams["enrltype{$stn}"] = $s;
                $stsql[] = "enrolment_type=:enrltype{$stn}";
                ++$stn;
            }

            if (!empty($stsql)) {
                $sttermwhere = ' AND ('.implode(' OR ', $stsql).') ';
            }

            $lsql = "SELECT class_object_id,class_name,class_weekday,class_url,location_name, agegroup_name,class_size FROM tbl_class LEFT JOIN tbl_location ON class_location_id = location_object_id LEFT JOIN tbl_agegroup ON class_agegroup_id = agegroup_id LEFT JOIN tbl_classterm ON classterm_class_id = class_object_id WHERE class_deleted IS NULL AND class_published = 1{$lwhere} AND agegroup_deleted IS NULL AND location_deleted IS NULL AND classterm_published = 1 AND classterm_deleted IS NULL {$cltermwhere} AND EXISTS (SELECT COUNT(DISTINCT (tmp.enrolment_student_id)) AS countnumber FROM tbl_enrolment AS tmp  WHERE tmp.enrolment_published = 1 AND tmp.enrolment_deleted IS NULL {$sttermwhere} AND tmp.enrolment_class_object_id = class_object_id AND tmp.enrolment_term_object_id = :term_object_id HAVING countnumber <= 3 )  ORDER BY location_suburb, FIELD(class_weekday, 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY', 'SUNDAY'), class_start_time, agegroup_name";
            $classes = $DBobject->wrappedSql($lsql, array_merge($lparams, $cltparams));

      //get all information for students enrolled in class days after today
            $sum = 0;
            foreach ($classes as &$c) {
                $rsql = "SELECT COUNT(DISTINCT(CONCAT(enrolment_student_id))) AS cnt FROM tbl_enrolment WHERE enrolment_published = 1 AND enrolment_deleted IS NULL {$sttermwhere} AND enrolment_published = 1 AND enrolment_class_object_id = :class_id AND enrolment_term_object_id = :term_id";
                $ressum = $DBobject->wrappedSql($rsql, array_merge(array(':class_id' => $c['class_object_id'], ':term_id' => $t['term_object_id']), $stparams));
                $c['enrolments'] = $ressum[0]['cnt'];
                $c['capacity'] = $ressum[0]['cnt'] / $c['class_size'];

                $sum += $ressum[0]['cnt'];
            }
            $l['total'] = $sum;
            if (!empty($classes)) {
                $l['classes'] = $classes;
            }
        }
        if (!empty($locations)) {
            $t['locations'] = $locations;
        }
    }

    return $termlist;
}

/**
 * Return SMS usage list group by admin users.
 * $startDate and $endDate must be in '%Y-%m-%d' format.
 *
 * @param string $startDate
 * @param string $endate
 *
 * @return array
 */
function smsusage($id = null, $terms = array(), $locations = array(), $filters = array(), $enrolmenttype = array(), $startDate, $endDate)
{
    global $DBobject;

    $params = array();
    $lwhere = '';
    if (!empty($_SESSION['user']['admin']['franchisee'])) {
        $n = 1;
        $tmpsql = array();
        foreach ($_SESSION['user']['admin']['franchisee'] as $m) {
            $tmpsql[] = "location_franchisee_id = :franchisee{$n}";
            $params[":franchisee{$n}"] = $m;
            ++$n;
        }
        $lwhere .= ' AND ('.implode(' OR ', $tmpsql).') ';
    }

    $tids = array();
    if (!empty($terms)) {
        $n = 1;
        foreach ($terms as $id) {
            $tids[] = "enrolment_term_object_id = :term_id{$n}";
            $params["term_id{$n}"] = $id;
            ++$n;
        }
        $lwhere .= ' AND ('.implode(' OR ', $tids).')';
    }
    $lids = array();
    if (!empty($locations)) {
        $n = 1;
        foreach ($locations as $id) {
            $lids[] = "class_location_id = :local_id{$n}";
            $params["local_id{$n}"] = $id;
            ++$n;
        }
        $lwhere .= ' AND ('.implode(' OR ', $lids).')';
    }
    if (!empty($filters) && !empty($filters['regions'])) {
        $rdsql = array();
        $nr = 0;
        foreach ($filters['regions'] as $rg) {
            $rdsql[] = "location_region_id=:region{$nr}";
            $params["region{$nr}"] = $rg;
            ++$nr;
        }
        $lwhere .= ' AND ('.implode(' OR ', $rdsql).') ';
    }
    if (!empty($filters) && !empty($filters['weekdays'])) {
        $wdsql = array();
        $dwn = 0;
        foreach ($filters['weekdays'] as $wd) {
            $params["weekday{$dwn}"] = $wd;
            $wdsql[] = "class_weekday like :weekday{$dwn}";
            ++$dwn;
        }
        $lwhere .= ' AND ('.implode(' OR ', $wdsql).') ';
    }
    if (!empty($filters) && !empty($filters['agegroups'])) {
        $agsql = array();
        $agn = 0;
        foreach ($filters['agegroups'] as $wd) {
            $params["agegroup{$agn}"] = $wd;
            $agsql[] = "class_agegroup_id=:agegroup{$agn}";
            ++$agn;
        }
        $lwhere .= ' AND ('.implode(' OR ', $agsql).') ';
    }
    $stn = 0;
    $stsql = array();
    if (!empty($enrolmenttype)) {
        foreach ($enrolmenttype as $s) {
            $params["enrltype{$stn}"] = $s;
            $stsql[] = "enrolment_type=:enrltype{$stn}";
            ++$stn;
        }
        $lwhere .= ' AND ('.implode(' OR ', $stsql).') ';
    }
    $sql_enrolments = " sms_user_id IN (SELECT DISTINCT(student_user_id) FROM tbl_student LEFT JOIN tbl_enrolment ON student_id = enrolment_student_id LEFT JOIN tbl_class ON enrolment_class_object_id = class_object_id LEFT JOIN tbl_location ON class_location_id = location_object_id WHERE location_deleted IS NULL AND class_deleted IS NULL AND enrolment_deleted IS NULL AND enrolment_published = '1' {$lwhere} ) ";

    $params['startDate'] = $startDate;
    $params['endDate'] = $endDate;

    $result = array();
    $sql = "SELECT sms_admin_id, admin_id, admin_level, admin_name, admin_surname, COUNT(sms_sent) as cnt FROM tbl_sms LEFT JOIN tbl_admin ON admin_id = sms_admin_id WHERE sms_sent = '1' AND DATE(sms_created) BETWEEN STR_TO_DATE(:startDate, '%Y-%m-%d') AND STR_TO_DATE(:endDate, '%Y-%m-%d') ".(empty($sql_enrolments) ? '' : "AND (sms_admin_id = '0' OR ({$sql_enrolments}))").' GROUP BY sms_admin_id';
    //if($_SERVER['REMOTE_ADDR']=='150.101.230.130') { var_dump($sql);var_dump($params); die();}
    if ($res = $DBobject->wrappedSql($sql, $params)) {
        foreach ($res as $r) {
            $result["{$r['sms_admin_id']}"] = $r;
            $fr = array();
            $sql = "SELECT franchisee_name FROM tbl_franchisee WHERE franchisee_object_id IN (SELECT DISTINCT(access_franchisee_id) FROM tbl_access WHERE access_deleted IS NULL AND access_flag = '1' AND access_admin_id = :access_admin_id ) AND franchisee_deleted IS NULL AND franchisee_published = '1'";
            if ($res2 = $DBobject->wrappedSql($sql, array('access_admin_id' => $r['admin_id']))) {
                foreach ($res2 as $r2) {
                    $fr[] = $r2['franchisee_name'];
                }
            }
            $result["{$r['sms_admin_id']}"]['franchise'] = ($r['admin_level'] == 1) ? '[Admin]' : implode(', ', $fr);
        }
    }

    return $result;
}

/**
 * Show how many (and percentage) of trials are booked through the front-end and admin area.
 *
 * Valid Filters:
 *
 *    [weekdays] = this is the day of the week (1=Monday, 2=Tuesday, 3=Wednesday, 4=Thursday, 5=Friday, 6=Saturday, 7=Sunday
 *    [agegroups] = age group id
 *    [time] = time of day in 24hr
 *
 * @param string $id
 * @param array  $terms     An array of the term ids to list
 * @param array  $locations An array of the location ids to list
 * @param int    $week      The week number representing a particular week of the term
 * @param array  $filters
 */
function triallist($id = null, $terms = array(), $locations = array(), $filters = array(), $enrolmenttype = array())
{
    global $DBobject;

    $fparams = array();
    if (!empty($_SESSION['user']['admin']['franchisee'])) {
        $n = 1;
        $tmpsql = array();
        foreach ($_SESSION['user']['admin']['franchisee'] as $m) {
            $tmpsql[] = "location_franchisee_id = :franchisee{$n}";
            $fparams[":franchisee{$n}"] = $m;
            ++$n;
        }
        $sql_franchisee = ' AND ('.implode(' OR ', $tmpsql).') ';
    }

    //This sql gets the term start day as the Sunday begining the week of the selected date SELECT DAYNAME(term_start_date), DAYOFWEEK(term_start_date),SUBDATE(term_start_date, (DAYOFWEEK(term_start_date)-1)),term_start_date FROM tbl_term
    if (empty($terms)) {
        //     $tsql = "SELECT term_object_id FROM tbl_term WHERE term_deleted IS NULL AND term_published = 1 AND term_start_date <= NOW() AND term_end_date >= NOW()";
        //     $res = $DBobject->wrappedSql($tsql);
        $tsql = "SELECT term_object_id FROM tbl_term WHERE term_deleted IS NULL AND term_published = 1 AND term_start_date <= NOW() AND term_end_date >= NOW() AND term_id IN (SELECT DISTINCT(classterm_term_id) FROM tbl_classterm LEFT JOIN tbl_class ON classterm_class_id = class_object_id WHERE class_deleted IS NULL AND classterm_deleted IS NULL AND classterm_published = 1 AND class_location_id IN (SELECT location_object_id FROM tbl_location WHERE location_deleted IS NULL {$sql_franchisee})) ORDER BY term_start_date DESC";
        $res = $DBobject->wrappedSql($tsql, $fparams);
        foreach ($res as $r) {
            $terms[] = $r['term_object_id'];
        }
        if (empty($terms)) {
            return array();
        }
    }
    $cltparams = array();
    //Get week numbers for all terms or assign static week number if selected
    $n = 1;
    $termlist = array();
    foreach ($terms as $id) {
        $wsql = 'SELECT term_name,term_region, term_id, term_object_id, SUBDATE(term_start_date, (DAYOFWEEK(term_start_date)-1)) AS firstsunday, '.(empty($week) ? 'IF(term_start_date < NOW(),CEIL(DATEDIFF(IF(term_end_date>NOW(),NOW(),term_end_date),SUBDATE(term_start_date, (DAYOFWEEK(term_start_date)-1)))/7),1)' : "{$week}").' AS weekno FROM tbl_term WHERE term_deleted IS NULL AND term_published = 1 AND term_object_id = :term_id';
        $tres = $DBobject->wrappedSql($wsql, array('term_id' => $id));
        $termlist[] = $tres[0];
        ++$n;
    }

    //Get Class record to show all classes for the selected term
    $lparams = array();
    $lids = array();
    $lwhere = '';
    $ln = 0;
    if (!empty($locations)) {
        foreach ($locations as $id) {
            $lids[] = "class_location_id = :local_id{$ln}";
            $lparams["local_id{$ln}"] = $id;
            ++$ln;
        }
        $lwhere = ' AND ('.implode(' OR ', $lids).')';
    }

    //Filter by region
    if (!empty($filters) && !empty($filters['regions'])) {
        $rdsql = array();
        $nr = 0;
        foreach ($filters['regions'] as $rg) {
            $fparams["region{$nr}"] = $rg;
            $rdsql[] = "location_region_id=:region{$nr}";
            ++$nr;
        }
        $sql_region = ' AND ('.implode(' OR ', $rdsql).') ';
    }

    foreach ($termlist as &$t) {
        $t['total'] = 0;
        $t['totalFront'] = 0;
        $t['totalBack'] = 0;

        $locsql = "SELECT * FROM tbl_location WHERE location_deleted IS NULL AND location_published = 1 {$sql_franchisee}{$sql_region} AND EXISTS (SELECT classterm_id FROM tbl_classterm LEFT JOIN tbl_class ON classterm_class_id = class_object_id WHERE class_location_id = location_object_id AND class_deleted IS NULL AND class_published = 1 AND classterm_deleted IS NULL AND classterm_published = 1 AND classterm_term_id = :term_id{$lwhere}) ORDER BY location_state, location_suburb";
        $locations = $DBobject->wrappedSql($locsql, array_merge($lparams, array(':term_id' => $t['term_id']), $fparams));
        foreach ($locations as &$l) {
            $cltermwhere = ' AND classterm_term_id = :term_id AND class_location_id = :loc_id';
            $cltparams['term_id'] = $t['term_id'];
            $cltparams['loc_id'] = $l['location_object_id'];

            if (!empty($filters) && !empty($filters['weekdays'])) {
                $wdsql = array();
                $dwn = 0;
                foreach ($filters['weekdays'] as $wd) {
                    $cltparams["weekday{$dwn}"] = $wd;
                    $wdsql[] = "class_weekday like :weekday{$dwn}";
                    ++$dwn;
                }
                $cltermwhere .= ' AND ('.implode(' OR ', $wdsql).') ';
            }
            if (!empty($filters) && !empty($filters['agegroups'])) {
                $agsql = array();
                $agn = 0;
                foreach ($filters['agegroups'] as $wd) {
                    $cltparams["agegroup{$agn}"] = $wd;
                    $agsql[] = "class_agegroup_id=:agegroup{$agn}";
                    ++$agn;
                }
                $cltermwhere .= ' AND ('.implode(' OR ', $agsql).') ';
            }
            $payparams = array();
            $paymwhere = '';
            if (!empty($filters) && !empty($filters['start_date'])) {
                $agsql = array();
                $payparams['start_date'] = date('Y-m-d', strtotime(str_replace('/', '-', $filters['start_date'])));
                $paymwhere .= ' AND DATE(payment_created) >= :start_date ';
            }
            if (!empty($filters) && !empty($filters['end_date'])) {
                $agsql = array();
                $payparams['end_date'] = date('Y-m-d', strtotime(str_replace('/', '-', $filters['end_date'])));
                $paymwhere .= ' AND DATE(payment_created) <= :end_date ';
            }

            $stn = 0;
            $stparams = array();
            $stsql = array();
            foreach ($enrolmenttype as $s) {
                $stparams["enrltype{$stn}"] = $s;
                $stsql[] = "enrolment_type=:enrltype{$stn}";
                ++$stn;
            }

            if (!empty($stsql)) {
                $sttermwhere = ' AND ('.implode(' OR ', $stsql).') ';
            }

            $lsql = "SELECT class_object_id,class_name,class_weekday,class_url,location_name, agegroup_name,agegroup_shortname,class_size FROM tbl_class LEFT JOIN tbl_location ON class_location_id = location_object_id LEFT JOIN tbl_agegroup ON class_agegroup_id = agegroup_id  WHERE class_deleted IS NULL AND class_published = 1{$lwhere} AND agegroup_deleted IS NULL AND location_deleted IS NULL AND EXISTS (SELECT classterm_id FROM tbl_classterm WHERE classterm_class_id = class_object_id AND classterm_deleted IS NULL {$cltermwhere}) ORDER BY location_suburb, FIELD(class_weekday, 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY', 'SUNDAY'), class_start_time, agegroup_name";
            $classes = $DBobject->wrappedSql($lsql, array_merge($lparams, $cltparams));

            //get all information for students enrolled in class days after today
            $sum = 0;
            $sumFront = 0;
            foreach ($classes as &$c) {
                //Total trials
                $rsql = "SELECT COUNT(DISTINCT(CONCAT(enrolment_student_id))) AS cnt FROM tbl_enrolment LEFT JOIN tbl_payment ON enrolment_payment_id = payment_id WHERE enrolment_deleted IS NULL {$sttermwhere} AND enrolment_class_object_id = :class_id AND enrolment_term_object_id = :term_id AND payment_deleted IS NULL {$paymwhere}";
                $ressum = $DBobject->wrappedSql($rsql, array_merge(array(':class_id' => $c['class_object_id'], ':term_id' => $t['term_object_id']), $stparams, $payparams));
                $c['trials'] = $ressum[0]['cnt'];
                $sum += $ressum[0]['cnt'];

                //Trials through front-end
                $rsql = "SELECT COUNT(DISTINCT(CONCAT(enrolment_student_id))) AS cnt FROM tbl_enrolment LEFT JOIN tbl_payment ON enrolment_payment_id = payment_id LEFT JOIN tbl_email_queue ON email_id = payment_invoice_email_id WHERE enrolment_deleted IS NULL {$sttermwhere} AND enrolment_class_object_id = :class_id AND enrolment_term_object_id = :term_id AND payment_deleted IS NULL {$paymwhere} AND (email_admin_id IS NULL OR email_admin_id = 0)";
                $ressum = $DBobject->wrappedSql($rsql, array_merge(array(':class_id' => $c['class_object_id'], ':term_id' => $t['term_object_id']), $stparams, $payparams));
                $c['trialsFront'] = $ressum[0]['cnt'];
                $sumFront += $ressum[0]['cnt'];
                $c['trialsFrontPer'] = round(($c['trialsFront'] / $c['trials']) * 100, 2);

                $c['trialsBack'] = $c['trials'] - $c['trialsFront'];
                $c['trialsBackPer'] = round(($c['trialsBack'] / $c['trials']) * 100, 2);
            }
            $l['total'] = $sum;
            $l['totalFront'] = $sumFront;
            $l['totalFrontPer'] = round(($sumFront / $sum) * 100, 2);
            $l['totalBack'] = $sum - $sumFront;
            $l['totalBackPer'] = round(($l['totalBack'] / $sum) * 100, 2);

            $t['total'] += $sum;
            $t['totalFront'] += $sumFront;
            $t['totalFrontPer'] = round(($t['totalFront'] / $t['total']) * 100, 2);
            $t['totalBack'] += $sum - $sumFront;
            $t['totalBackPer'] = round(($t['totalBack'] / $t['total']) * 100, 2);
            if (!empty($classes)) {
                $l['classes'] = $classes;
            }
        }
        if (!empty($locations)) {
            $t['locations'] = $locations;
        }
    }

    return $termlist;
}

/**
 * Make a payment report.
 *
 * Valid Filters:
 *
 * @param array $filters
 */
function makeapaymentlist($filters = array())
{
    global $DBobject;

    $params = array();
    if (!empty($_SESSION['user']['admin']['franchisee'])) {
        $n = 1;
        $tmpsql = array();
        foreach ($_SESSION['user']['admin']['franchisee'] as $m) {
            $tmpsql[] = "cartitem_attr_attr_value_name = :franchisee{$n}";
            $params[":franchisee{$n}"] = $m;
            ++$n;
        }
        $sql_franchisee = ' AND ('.implode(' OR ', $tmpsql).') ';
    } else {
        if (!empty($filters) && !empty($filters['franchisee'])) {
            $n = 1;
            $tmpsql = array();
            foreach ($filters['franchisee'] as $f) {
                if (empty($f)) {
                    continue;
                }
                $tmpsql[] = "cartitem_attr_attr_value_name = :franchisee{$n}";
                $params[":franchisee{$n}"] = $f;
                ++$n;
            }
            if (!empty($tmpsql)) {
                $sql_franchisee = ' AND ('.implode(' OR ', $tmpsql).') ';
            }
        }
    }

    //Date range
    $paymwhere = '';
    if (!empty($filters) && !empty($filters['start_date'])) {
        $params['start_date'] = date('Y-m-d', strtotime(str_replace('/', '-', $filters['start_date'])));
        $paymwhere .= ' AND DATE(order_created) >= :start_date ';
    }
    if (!empty($filters) && !empty($filters['end_date'])) {
        $params['end_date'] = date('Y-m-d', strtotime(str_replace('/', '-', $filters['end_date'])));
        $paymwhere .= ' AND DATE(order_created) <= :end_date ';
    }

    $paysql = "SELECT paytype_name, payment_created, cartitem_product_name, user_gname, user_surname, user_email, address_telephone, payment_total, payment_surcharge, status_name, order_created FROM tbl_payment
		LEFT JOIN tbl_user ON user_id = payment_user_id
		LEFT JOIN tbl_cartitem ON cartitem_cart_id = payment_cart_id
		LEFT JOIN tbl_paytype ON cartitem_product_id = paytype_id
		LEFT JOIN tbl_address ON payment_billing_address_id = address_id
		LEFT JOIN tbl_order ON payment_id = order_payment_id
		LEFT JOIN tbl_status ON order_status_id = status_id
		WHERE user_deleted IS NULL AND cartitem_deleted IS NULL AND address_deleted IS NULL AND
		order_deleted IS NULL AND status_deleted IS NULL AND payment_deleted IS NULL AND payment_status = 'A' 
		AND cartitem_product_id != -2
		AND paytype_deleted IS NULL AND paytype_id IS NOT NULL {$paymwhere} ORDER BY payment_created DESC";

    if (!empty($sql_franchisee)) {
        $paysql = "SELECT paytype_name, payment_created, cartitem_product_name, user_gname, user_surname, user_email, address_telephone, payment_total, payment_surcharge, status_name, order_created FROM tbl_payment 
			LEFT JOIN tbl_user ON user_id = payment_user_id 
			LEFT JOIN tbl_cartitem ON cartitem_cart_id = payment_cart_id 
			LEFT JOIN tbl_paytype ON cartitem_product_id = paytype_id
			LEFT JOIN tbl_cartitem_attr ON cartitem_attr_cartitem_id = cartitem_id  
			LEFT JOIN tbl_address ON payment_billing_address_id = address_id
			LEFT JOIN tbl_order ON payment_id = order_payment_id
			LEFT JOIN tbl_status ON order_status_id = status_id
			WHERE user_deleted IS NULL AND cartitem_deleted IS NULL AND cartitem_attr_deleted IS NULL 
			AND cartitem_product_id != -2
			AND address_deleted IS NULL AND order_deleted IS NULL AND status_deleted IS NULL 
			AND payment_deleted IS NULL AND payment_status = 'A' AND cartitem_attr_attribute_name = 'franchisee_id' 
		AND paytype_deleted IS NULL AND paytype_id IS NOT NULL {$sql_franchisee}{$paymwhere} ORDER BY payment_created DESC";
    }

    return $DBobject->wrappedSql($paysql, $params);
}
